Identify duplicates

[Anonymous]

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

[sunny_talwar]

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;

[Anonymous]

Thank you for the quick response Sunny.

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

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

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

[maxgro · Accepted solution]

Z:

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;

[Anonymous]

Fantastic, this works!

Thank you so much maxgro!

[joancasellasvega]

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