topic Re: identifying Duplicates in QlikView

identifying Duplicates

simonwithey — Wed, 25 Nov 2020 16:16:04 GMT

As a Qlik virgin I need the help of you experts. I need to identify and set a flag against a duplicate record in a load script. Example ogf the table below. I need to check if the Id's are the same and if so i need to set a flag against the oldest date only and set the later date as OK. If the dates are equal i just need to set the first record as a duplicate. many thanks

Id	Auth Date
C0018337	19-Jul-17
C0018337	27-Jul-17
CX211795	02-Jun-17
CX211795	02-Jun-17
CX212198	08-Jun-17
CX212198	09-Jun-17
N0002488	08-Jun-17
N0002488	14-Jun-17
P0000740	12-Jun-17
P0000740	16-Jun-17
R0000249	06-Jul-17
R0000249	25-Jul-17

Re: identifying Duplicates

sunny_talwar — Fri, 11 Aug 2017 15:32:07 GMT

May be this

SET DateFormat='DD-MMM-YY';

Table:

LOAD * INLINE [

Id, Auth Date

C0018337, 19-Jul-17

C0018337, 27-Jul-17

CX211795, 02-Jun-17

CX212198, 08-Jun-17

CX212198, 09-Jun-17

N0002488, 08-Jun-17

N0002488, 14-Jun-17

P0000740, 12-Jun-17

P0000740, 16-Jun-17

R0000249, 06-Jul-17

R0000249, 25-Jul-17

];

FinalTable:

LOAD Id,

[Auth Date],

If(Id = Previous(Id), 'Duplicate') as Flag

Resident Table

Order By Id, [Auth Date] desc;

DROP Table Table;

Re: identifying Duplicates

dan_sullivan — Fri, 11 Aug 2017 15:38:43 GMT

This is good advice from Sunny. you can also do a load distinct to just eliminate the duplicates where they have the same dates and Id.

Re: identifying Duplicates

effinty2112 — Fri, 11 Aug 2017 15:49:14 GMT

Hi Simon,

Another way:

Data:

LOAD * Inline [

Id, Auth Date

C0018337, 19-Jul-17

C0018337, 27-Jul-17

CX211795, 02-Jun-17

CX212198, 08-Jun-17

CX212198, 09-Jun-17

N0002488, 08-Jun-17

N0002488, 14-Jun-17

P0000740, 12-Jun-17

P0000740, 16-Jun-17

R0000249, 06-Jul-17

R0000249, 25-Jul-17

];

Left Join(Data)

LOAD

Id,

min([Auth Date],2) as [Auth Date],

'OK' as Flag

Resident Data Group by Id;

gives:

Id	Auth Date	Flag
C0018337	27-Jul-17	OK
C0018337	19-Jul-17
CX211795	02-Jun-17
CX212198	09-Jun-17	OK
CX212198	08-Jun-17
N0002488	14-Jun-17	OK
N0002488	08-Jun-17
P0000740	16-Jun-17	OK
P0000740	12-Jun-17
R0000249	25-Jul-17	OK
R0000249	06-Jul-17

cheers

Andrew

Re: identifying Duplicates

dan_sullivan — Fri, 11 Aug 2017 19:54:09 GMT

Simon can you mark the answer as correct and close thread if you are satisfied with the results?

Re: identifying Duplicates

simonwithey — Mon, 14 Aug 2017 10:34:53 GMT

I will once I've had chance to have a play. I am a complete novice so I'm a bit slow at the moment. What I'm not sure about the responses is that the table will contain over 20,000 records (of which only about 12 will be duplicates) and the the two fields in question will not be sorted so I'm not sure if this will be an issue with the above solutions.

Re: identifying Duplicates

simonwithey — Mon, 14 Aug 2017 12:52:05 GMT

Thank you Sunny. Apologies for not making myself clear. I won't be typing the data in, it will be part of a non sorted data table/file.

Re: identifying Duplicates

simonwithey — Mon, 14 Aug 2017 12:52:19 GMT

Thank you Sunny. Apologies for not making myself clear. I won't be typing the data in, it will be part of a non sorted data table/file.

Re: identifying Duplicates

sunny_talwar — Mon, 14 Aug 2017 13:26:17 GMT

We know that.... the above was just to show how it can be done.... in your case, you will do something like this

SET DateFormat='DD-MMM-YY';

Table:

LOAD *

FROM .....;

FinalTable:

LOAD Id,

[Auth Date],

If(Id = Previous(Id), 'Duplicate') as Flag,

....

Resident Table

Order By Id, [Auth Date] desc;

DROP Table Table;

Red part is where you will load the data from whatever you are loading it from

Re: identifying Duplicates

simonwithey — Mon, 14 Aug 2017 14:03:51 GMT

Thanks Sunny, I'm still having issues. I've tried copying your syntax but I'm obviously doing something wrong.

The table is already loaded (called REP). Can I ask you to spell out the red text in idiot terms for me please. As soon as I type FROM [REP] it doesn't like it. Sorry to be a pain

Re: identifying Duplicates

Miguel_Angel_Baeyens — Mon, 14 Aug 2017 14:11:02 GMT

Where are you loading the data from: database, file?

Simple example for database:

CONNECT TO [Database]; // Use the button "Connect..." in the script editor for the wizard

Table:

LOAD *;

// Put your sql statement here to retrieve the rows

SQL SELECT *

FROM Table;

Simple example for file:

Table:

LOAD *

FROM File.qvd (Qvd); // type of file and options can be different, use "Table Files" button in the script editor

Re: identifying Duplicates

simonwithey — Mon, 14 Aug 2017 14:27:58 GMT

The file is already loaded (see example below). It's saying that 'Table' doesn't exist. If you could fill in where and what I should be entering to make it work I'd be very grateful. Many thanks in advance.

[REP]:

LOAD

"Assessment No",

"Creator Name",

"Vehicle Manufacturer Name",

"Vehicle Model Name",

"Creator Site Id",

"Creator Terminal Id",

"Vehicle Reg",

"First Auth Date",

FROM [lib://VIA Motor Drive (Perf & Planning)/Motor Repair\MOTOR REPAIR 2017.xlsx]

(ooxml, embedded labels, table is REP);

Table:

Load

"Assessment No",

"First Auth Date";

FinalTable:

Load

"Assessment No",

"First Auth Date",

If("Assessment No"=Previous("Assessment No"),'DUP','')as [DUP Flag]

Resident Table

Order By "Assessment No",[First Auth Date]desc;

Drop Table Table;

Re: identifying Duplicates

sunny_talwar — Mon, 14 Aug 2017 14:34:38 GMT

You will resident from REP instead of Table

[REP]:

LOAD

"Assessment No",

"Creator Name",

"Vehicle Manufacturer Name",

"Vehicle Model Name",

"Creator Site Id",

"Creator Terminal Id",

"Vehicle Reg",

"First Auth Date",

FROM [lib://VIA Motor Drive (Perf & Planning)/Motor Repair\MOTOR REPAIR 2017.xlsx]

(ooxml, embedded labels, table is REP);

Final_REP:

Load *,

If("Assessment No"=Previous("Assessment No"),'DUP','')as [DUP Flag]

Resident REP

Order By "Assessment No", [First Auth Date] desc;

Drop Table REP;

Re: identifying Duplicates

simonwithey — Mon, 14 Aug 2017 14:56:02 GMT

Cheers Sunny, looks like I'm nearly there. I just need to do some tweaking as to whereabouts I put the code as I have a lot more to identify with other criteria. But many many thanks for your help and patience.

Re: identifying Duplicates

sunny_talwar — Mon, 14 Aug 2017 14:56:51 GMT

No problem Simon, one of us will always be there to help you out.

Best,

Sunny

Re: identifying Duplicates

passionate — Mon, 14 Aug 2017 15:12:51 GMT

Hi Simon,

PFA, Solution.

Regards,

Pankaj

Re: identifying Duplicates

Miguel_Angel_Baeyens — Mon, 14 Aug 2017 16:19:39 GMT

Another way is loading your table building a key field or flag field with all what identifies a duplicate, and on a preceding load, use the Exists() function to search for it and load only relevant values:

Table:

LOAD

WHERE NOT Exists(Key)

;

LOAD

,Field1 & '|' & Field2 & '|' & Field 3 AS Key

FROM File;

EDIT: Example attached for perusal

Re: identifying Duplicates

upaliwije — Tue, 15 Aug 2017 04:13:15 GMT

Dear Sunny

I am also interested in your script of finding duplicates.

If("Assessment No"=Previous("Assessment No"),'DUP','')as [DUP Flag]

if I am not mistaken Pls educate me what does ,'' part do in script (shown in red colour above)

Thanks

Re: identifying Duplicates

Anil_Babu_Samineni — Tue, 15 Aug 2017 04:20:51 GMT

May be sunny sleeping as he located in USA. Why don't you test in your end rather expecting from community

Here, The part you are expecting works as

Sample Data

FieldName

If(FieldName = Previous(FieldName), 'DUP', ' ') as Flag

Output will come like below

FieldName, Flag

1, Space // Because this is not the part the your if condition fulfilled.

2, Space

3, Space

3, DUP

Rather, You can test for your case

If(FieldName = Previous(FieldName), 'DUP', 'Not DUP') as Flag

Re: identifying Duplicates

upaliwije — Tue, 15 Aug 2017 06:32:23 GMT

Thanks