topic Removing Duplicate Comma Separated Values in QlikView

Removing Duplicate Comma Separated Values

haymarketpaul — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Removing Duplicate Comma Separated Values

MK_QSL — Wed, 22 Jun 2016 14:09:42 GMT

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

Re: Removing Duplicate Comma Separated Values

sunny_talwar — Wed, 22 Jun 2016 14:10:01 GMT

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

Re: Removing Duplicate Comma Separated Values

haymarketpaul — Wed, 22 Jun 2016 14:13:12 GMT

Thanks both - almost like you copied each other

I'll give this a go

Re: Removing Duplicate Comma Separated Values

haymarketpaul — Thu, 18 Aug 2016 14:19:09 GMT

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