Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

achakilam1022
Contributor

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.

Tags (1)
1 Solution

Accepted Solutions

Re: Finding duplicates in a column

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;

10 Replies

Re: Finding duplicates in a column

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

achakilam1022
Contributor

Re: Finding duplicates in a column

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

qlik.PNG

Re: Finding duplicates in a column

Why is this dup?

Capture.PNG

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

achakilam1022
Contributor

Re: Finding duplicates in a column(QlikSense)

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

Re: Finding duplicates in a column

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
Contributor

Re: Finding duplicates in a column(QlikSense)

Thanks for the reply.

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

Re: Finding duplicates in a column(QlikSense)

It is a valid function

Previous - script function ‒ Qlik Sense

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

achakilam1022
Contributor

Re: Finding duplicates in a column(QlikSense)

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

Re: Finding duplicates in a column(QlikSense)

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

Best,

Sunny

Community Browser