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: 
Anonymous
Not applicable

I need to identify duplicates and put result in another column, Help please

KEY
0943906193201829.5
094390619320183
094390619320183.44
094390619320188.72
09439062022018159
09439062022018159
094390622201812.87
0943906222018122.63
0943906222018818.01
094390622201899
KEYDupResult
0943906193201829.5OK
094390619320183OK
094390619320183.44OK
094390619320188.72OK
09439062022018159OK
09439062022018159DUP
094390622201812.87OK
0943906222018122.63OK
0943906222018818.01OK
094390622201899OK
1 Solution

Accepted Solutions
maxgro
MVP
MVP

T:

load * inline [

KEY

0943906193201829.5

094390619320183

094390619320183.44

094390619320188.72

09439062022018159

09439062022018159

094390622201812.87

0943906222018122.63

0943906222018818.01

094390622201899

];

T2:

load

KEY,

if(KEY=Peek('KEY'), 'DUP', 'OK') as DupResult

Resident T

order by KEY;

drop table T;

View solution in original post

6 Replies
Anonymous
Not applicable
Author

To add clarity, I want the first instance to be OK but any duplicates to show as DUP.

maxgro
MVP
MVP

T:

load * inline [

KEY

0943906193201829.5

094390619320183

094390619320183.44

094390619320188.72

09439062022018159

09439062022018159

094390622201812.87

0943906222018122.63

0943906222018818.01

094390622201899

];

T2:

load

KEY,

if(KEY=Peek('KEY'), 'DUP', 'OK') as DupResult

Resident T

order by KEY;

drop table T;

Anonymous
Not applicable
Author

Thanks Massimo,

I have thousands of entries in KEY field

Do I need to list them all, as above?

lanlizgu
Creator III
Creator III

Why don't you create a table with KEY as a dimension and add an expression counting the KEYs?

In case of being bigger than 1 this would mean that the field is duplicated.

lanlizgu
Creator III
Creator III

you can add an if to the previous expression so you could have these keys labeled as OK and DUP.

maxgro
MVP
MVP

you only need

T2:

load

KEY,

if(KEY=Peek('KEY'), 'DUP', 'OK') as DupResult

Resident YOURTABLE

order by KEY;

drop table YOURTABLE;


after the load of YOURTABLE

I added the inline load to check my script with your data; replace the inline load with the load of your data