topic Identify duplicates in QlikView

Identify duplicates

Anonymous — Wed, 18 Nov 2015 19:11:33 GMT

Hello,

I am trying to find duplicate records within my table. I have created a flag that successfully identifies the duplicates, however, there is a null value in the 2nd row of the duplicate record.

How can I have the flag show a 1 for both records while leaving a null value, or 0, in the distinct account?

Here is my script and an example of the output I currently have.

LOAD *,

if(Peek(Account) = Account,1) as DuplicateFlag

Resident Events

Order by Account Asc;

DROP Table Events;

Date	Account	DuplicateFlag
7/14/2015	Account1	1
7/14/2015	Account1	-
7/14/2015	Account2	-

EDIT: What I am trying to show is this:

Date	Account	DuplicateFlag
7/14/2015	Account1	1
7/14/2015	Account1	1
7/14/2015	Account2	0

Thank you in advance for your help

Re: Identify duplicates

sunny_talwar — Wed, 18 Nov 2015 19:16:01 GMT

Try this:

LOAD *,

If(Len(Trim(DuplicateFlag)) = 0, 0, DuplicateFlag) as DupFlag;

LOAD *,

if(Peek(Account) = Account,1) as DuplicateFlag

Resident Events

Order by Account Asc;

DROP Table Events;

DROP Field DuplicateFlag;

RENAME Field DupFlag to DuplicateFlag;

Re: Identify duplicates

Anonymous — Wed, 18 Nov 2015 19:22:06 GMT

Thank you for the quick response Sunny.

This is better, however there is still a 0 for account1.

7/14/2015

Account1

7/14/2015

Account1

7/14/2015

Account2

What I'm trying to show is this:

Date	Account	DuplicateFlag
7/14/2015	Account1	1
7/14/2015	Account1	1
7/14/2015	Account2	0

Re: Identify duplicates

maxgro — Wed, 18 Nov 2015 19:24:09 GMT

LOAD Date,

Account

FROM

[https://community.qlik.com/thread/192024]

(html, codepage is 1252, embedded labels, table is @1);

left join (Z)

load Date, Account, if(count(Account)>1,1,0) as DuplicateFlag

Resident Z

group by Date, Account;

Re: Identify duplicates

Anonymous — Wed, 18 Nov 2015 19:30:59 GMT

Fantastic, this works!

Thank you so much maxgro!

Re: Identify duplicates

joancasellasvega — Wed, 27 May 2020 10:37:56 GMT

Hi @sunny_talwar ,

it is possible to create a filter (in Qlik Sense) to identfy duplicates and keep INSTANCIA with the latest LastModified date.

Ticket Id	INSTANCIA	LastModified	Duplicate
61951	401574183	26/05/2020	0
1157	401574183	27/05/2020	1

I don't want to drop the duplicates, but hide them by using the filter, when showing results in a table.

thanks,

Joan