Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

r15hnm777
New Contributor III

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
jsf_fasoli
Contributor II

Re: How to flag Duplicate records in Script

Hi Rishi,

¿Has your table got a key field?

r15hnm777
New Contributor III

Re: How to flag Duplicate records in Script

Hi Josefina

No the table doesn't contain a Key field

kaanerisen
Contributor III

Re: How to flag Duplicate records in Script

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

Re: How to flag Duplicate records in Script

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

Community Browser