Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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 |
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;
Fantastic, this works!
Thank you so much maxgro!
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