topic Re: How to flag Duplicate records in Script in App Development

How to flag Duplicate records in Script

Anonymous — Tue, 16 Jan 2018 19:37:25 GMT

Emp_No.	Date	Amt	Duplicate
00001	1/1/2018	5.00
00001	1/2/2018	10.00
00001	1/1/2018	5.00	X
00002	4/2/2017	4.00
00002	4/2/2017	20.00
00003	3/1/2018	7.00	X
00003	3/1/2018	7.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

Re: How to flag Duplicate records in Script

Anonymous — Tue, 16 Jan 2018 20:54:43 GMT

Hi Rishi,

¿Has your table got a key field?

Re: How to flag Duplicate records in Script

Anonymous — Tue, 16 Jan 2018 21:46:39 GMT

Hi Josefina

No the table doesn't contain a Key field

Re: How to flag Duplicate records in Script

kaanerisen — Wed, 17 Jan 2018 05:01:53 GMT

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

];

drop field id From Test;

Re: How to flag Duplicate records in Script

marcus_sommer — Wed, 17 Jan 2018 08:33:52 GMT

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