topic Re: Identifying duplicate records using the script in QlikView

Identifying duplicate records using the script

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

I want to identify duplicate records. I've used the Peek and Previous function but not getting the desired result.

I have a table below with field Unique ID. I want to create another field showing which values are duplicates. ( 1 if there is a duplicate) - see 2nd table below. How do I do do this?

Unique ID

Unique ID	Duplicate
A1	1
A2
A3
A4
A5	1
A6	1
A7
A1	1
A5	1
A6	1
A8
A9

Kind regards

Nayan

Re: Identifying duplicate records using the script

kaushiknsolanki — Wed, 24 Jan 2018 06:56:54 GMT

Try this,

Sample:

Load * inline [

UniqueID

];

Temp:

Load * where Count>=2;

Load UniqueID as UID,Count(UniqueID) as Count

Resident Sample

Group by UniqueID;

Final:

Load *,If(Exists(UID,UniqueID),1,0) as DuplicateFlag

Resident Sample;

Drop table Sample, Temp;

Regards,

Kaushik Solanki

Re: Identifying duplicate records using the script

tamilarasu — Wed, 24 Jan 2018 07:08:42 GMT

Hi Nayan,

Try this,

Data:

Load * inline [

UniqueID

];

Left Join (Data)

Load UniqueID,

If(Count(UniqueID)>1,1) as Count

Resident Data

Group by UniqueID;

Re: Identifying duplicate records using the script

devarasu07 — Wed, 24 Jan 2018 07:11:10 GMT

Hi,

try below method

Data:
LOAD * INLINE [
    Unique ID
    A1
    A2
    A3
    A4
    A5
    A6
    A7
    A1
    A5
    A6
    A8
    A9
];


left join (Data)
load distinct [Unique ID],if(count([Unique ID])>1,1,0)  as Flag Resident Data group by [Unique ID];

Re: Identifying duplicate records using the script

Anonymous — Wed, 24 Jan 2018 08:28:16 GMT

Hi Kaushik

I've applied your script on a test model and it worked. Some how its not working with the model I'm busy with. My unique ID is a concatenation of many fields. Let me drill down to the detail of each field.

Kind regards

Nayan

Re: Identifying duplicate records using the script

kaushiknsolanki — Wed, 24 Jan 2018 09:01:07 GMT

Sure.

Regards,

Kaushik Solanki

Re: Identifying duplicate records using the script

Clever_Anjos — Wed, 24 Jan 2018 10:01:59 GMT

Another approach:

Base:
LOAD [Unique ID],
IF(Exists([Unique ID]),1) as Flag
FROM [https://community.qlik.com/thread/288848](html, codepage is 1252, embedded labels, table is @1);

m:
mapping Load
[Unique ID],
sum(Flag) as Flag
Resident Base
Group By [Unique ID];

Final:
Load
[Unique ID],
ApplyMap('m',[Unique ID]) as Duplicate
Resident Base;
Drop Table Base;

Re: Identifying duplicate records using the script

Anonymous — Wed, 24 Jan 2018 10:26:15 GMT

Thank you all. All solutions given has worked.

Kind regards

Nayan