Removing Duplicate Comma Separated Values

[haymarketpaul]

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...

ID	Topics
1	Allergies,Cancer,Cancer,Cancer,Cardiology,Cardiology,Men's Health,Dermatology,Dermatology
2	ADHD,ADHD,Cancer,Arthritis,Arthritis,Endocrinology,Depression,Depression,Depression,Diabetes
3	Allergies,ADHD,Cancer,Cancer,Cardiology,Men's Health,Dermatology,Dermatology,Dermatology,Dermatology
4	Allergies,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....

ID	Topics
1	Allergies,Cancer,Cardiology,Men's Health,Dermatology
2	ADHD,Cancer,Arthritis,Endocrinology,Depression,Diabetes
3	Allergies,ADHD,Cancer,Cardiology,Men's Health,Dermatology
4	Allergies,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

[MK_QSL · Accepted solution]

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);

[haymarketpaul]

Thanks both - almost like you copied each other

I'll give this a go

[haymarketpaul]

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