Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Identify duplicates

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/2015Account11
7/14/2015Account1-
7/14/2015Account2-

EDIT:    What I am trying to show is this:

DateAccountDuplicateFlag
7/14/2015Account11
7/14/2015Account11
7/14/2015Account20

Thank you in advance for your help

1 Solution

Accepted Solutions
maxgro
MVP
MVP

1.png


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;

View solution in original post

5 Replies
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
Not applicable
Author

Thank you for the quick response Sunny.

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

Date

AccountDuplicateFlag
7/14/2015Account11
7/14/2015Account10
7/14/2015Account20

What I'm trying to show is this:

DateAccountDuplicateFlag
7/14/2015Account11
7/14/2015Account11
7/14/2015Account20
maxgro
MVP
MVP

1.png


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
Not applicable
Author

Fantastic, this works!

Thank you so much maxgro!

joancasellasvega
Partner - Contributor III
Partner - Contributor III

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 IdINSTANCIALastModifiedDuplicate
6195140157418326/05/20200
115740157418327/05/20201

 

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

thanks,

Joan