Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a data table containing columns id, date, phone and the column flag which i want to modify (1 for unique and 0 for duplicate)
I want to find duplicates in phone column and mark flag based on date. Eg: If the same phone number appears thrice in the column then the earliest record (based on date) should be marked as 1 and remaining two records(duplicates) should be marked as 0.
Expected Output : date format : dd/mm/yyyy
thank you.
Try this
Table:
LOAD * INLINE [
id, date, phone
1, 1/1/2017, a
2, 2/1/2017, d
3, 4/1/2017, a
4, 5/1/2017, a
5, 1/1/2017, d
];
FinalTable:
LOAD *,
If(phone = Previous(phone), 'dup', 'unique') as Flag
Resident Table
Order By phone, date;
DROP Table Table;
Would you be able to share a sample to check this out?
I want to populate the flag column as shown in the below image
Why is this dup?
Is it because you are sorting the data in ascending order?
Yes, as a record with same phone number has been recorded on an earlier date. Date format is dd/mm/yyyy. Sorry for not mentioning that
Try this
Table:
LOAD * INLINE [
id, date, phone
1, 1/1/2017, a
2, 2/1/2017, d
3, 4/1/2017, a
4, 5/1/2017, a
5, 1/1/2017, d
];
FinalTable:
LOAD *,
If(phone = Previous(phone), 'dup', 'unique') as Flag
Resident Table
Order By phone, date;
DROP Table Table;
Thanks for the reply.
I'm using qliksense and it gives me a error...Previous is not a valid function
It is a valid function
Previous - script function ‒ Qlik Sense
Can you share the error message and the exact script you used?
Oops. I see what i did wrong. It works. Thanks a lot Sunny
Awesome, please close the thread by marking correct and helpful responses if you got what you wanted.
Best,
Sunny