Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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);
Thanks both - almost like you copied each other
I'll give this a go
Finally got around to implementing this and works perfectly so thank you both