Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
napac1458
Contributor II

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
MVP
MVP

Re: Identify duplicates

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;

4 Replies

Re: Identify duplicates

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;

napac1458
Contributor II

Re: Identify duplicates

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
MVP
MVP

Re: Identify duplicates

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;

napac1458
Contributor II

Re: Identify duplicates

Fantastic, this works!

Thank you so much maxgro!