Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
achakilam1022
Creator II
Creator II

Finding duplicates in a column(QlikSense)

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

qlik.PNG

thank you.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

10 Replies
sunny_talwar

Would you be able to share a sample to check this out?

achakilam1022
Creator II
Creator II
Author

I want to populate the flag column as shown in the below image

qlik.PNG

sunny_talwar

Why is this dup?

Capture.PNG

Is it because you are sorting the data in ascending order?

achakilam1022
Creator II
Creator II
Author

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

sunny_talwar

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;

achakilam1022
Creator II
Creator II
Author

Thanks for the reply.

I'm using qliksense and it gives me a error...Previous is not a valid function

sunny_talwar

It is a valid function

Previous - script function ‒ Qlik Sense

Can you share the error message and the exact script you used?

achakilam1022
Creator II
Creator II
Author

Oops. I see what i did wrong. It works. Thanks a lot Sunny

sunny_talwar

Awesome, please close the thread by marking correct and helpful responses if you got what you wanted.

Best,

Sunny