Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to flag Duplicate records in Script

Emp_No.



DateAmtDuplicate
000011/1/20185.00
000011/2/201810.00
000011/1/20185.00X
000024/2/20174.00
000024/2/201720.00
000033/1/20187.00X
000033/1/20187.00

Hi All

I have the above data, I basically need to create another column that flags all the rows which are duplicates based on Emp_No., Date, Amt being the same for reporting purposes.

I have tried various methods including the technique but this has a huge impact on load time.

autonumber(Recno(), [Emp_No.] & '-' & [Date] & '-' & Amt) > 1 as Duplicate

Any help would be much appreciated.

Thanks

4 Replies
Anonymous
Not applicable
Author

Hi Rishi,

¿Has your table got a key field?

Anonymous
Not applicable
Author

Hi Josefina

No the table doesn't contain a Key field

kaanerisen
Creator III
Creator III

Hi Rishi,

You can try like this.

Test:

Load id,EmpNo,Date,Amt,

IF(not ISNULL(Lookup('Amt',id, id)),'x') as Flag;

Load

AutoNumber(EmpNo&Date&Amt) as id,* Inline [

EmpNo,Date,Amt

00001,1/1/2018,5.00

00001,1/2/2018,10.00

00001,1/1/2018,5.00

00002,4/2/2017,4.00

00002,4/2/2017,20.00

00003,3/1/2018,7.00

00003,3/1/2018,7.00

];

drop field id From Test;

Untitled.png

marcus_sommer

In a properly sorted resident-load you could use interecord-functions like Peek() or Previous() ? and there you could use an expression like:

-rangemin([Emp_No.]<>previous([Emp_No.]), [Date]<>previous([Date]), Amt<>previous(Amt)) as DuplicateFlag


If your load-times are further too slow you will need an incremental load-approach. Within the last two link-blocks in this document you will find various informations about this topic: Advanced topics for creating a qlik datamodel.


- Marcus