Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
haymarketpaul
Creator III
Creator III

Removing Duplicate Comma Separated Values

QV12 SR3

I am being given data in the following comma separated format (within an Excel file) which has a lot of duplication of answers within it...

IDTopics
1Allergies,Cancer,Cancer,Cancer,Cardiology,Cardiology,Men's Health,Dermatology,Dermatology
2ADHD,ADHD,Cancer,Arthritis,Arthritis,Endocrinology,Depression,Depression,Depression,Diabetes
3Allergies,ADHD,Cancer,Cancer,Cardiology,Men's Health,Dermatology,Dermatology,Dermatology,Dermatology
4Allergies,Cancer,Cancer,Cancer,Hemotology,Hemotology,Cardiology,Men's Health,Dermatology,Dermatology,Pediatrics

I only need one of each answer per ID so based on the above would end up with this sort of thing....

IDTopics
1Allergies,Cancer,Cardiology,Men's Health,Dermatology
2ADHD,Cancer,Arthritis,Endocrinology,Depression,Diabetes
3Allergies,ADHD,Cancer,Cardiology,Men's Health,Dermatology
4Allergies,Cancer,Hemotology,Cardiology,Men's Health,Dermatology,Pediatrics

Is there an easyish way to remove duplicate answers per ID within the LOAD script ?

Any ideas much appreciated

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Data:

LOAD ID, Concat(Distinct Topics, ',') as Topics Group By ID;

LOAD ID,

     SubField(Topics,',') as Topics

FROM

[https://community.qlik.com/thread/222064]

(html, codepage is 1252, embedded labels, table is @1);

View solution in original post

4 Replies
MK_QSL
MVP
MVP

Data:

LOAD ID, Concat(Distinct Topics, ',') as Topics Group By ID;

LOAD ID,

     SubField(Topics,',') as Topics

FROM

[https://community.qlik.com/thread/222064]

(html, codepage is 1252, embedded labels, table is @1);

sunny_talwar

Try this:

Table:

LOAD ID,

  Concat(DISTINCT Topics, ',') as Topics

Group By ID;

LOAD ID,

     SubField(Topics, ',') as Topics

FROM

[https://community.qlik.com/thread/222064]

(html, codepage is 1252, embedded labels, table is @1);


Capture.PNG

haymarketpaul
Creator III
Creator III
Author

Thanks both - almost like you copied each other

I'll give this a go

haymarketpaul
Creator III
Creator III
Author

Finally got around to implementing this and works perfectly so thank you both