topic Re: Qlik Sense: Separating comma separated values into columns by category in Water Cooler

Qlik Sense: Separating comma separated values into columns by category

Anonymous — Tue, 18 Dec 2018 03:48:26 GMT

Hi All,

I'm a very new user and was wondering if within QlikSense i am able to separate data that comes in comma separate values as per table A, into Table B? Looking to do this within QlikSense as table A is coming from an SQL search.

Table A (current)

Customer	Areas for improvement
AA001	Allergies, BMI, BP, Waist
AA002	Alcohol, BMI, Waist
BB001	Allergies, BP, Physical Activity

Table B (desired)

Customer	Allergies Group	Alcohol Group	BMI	BP	Physical Activity	Waist
AA001	Allergies		BMI	BP		Waist
AA002		Alcohol	BMI			Waist
BB001	Allergies			BP	Physical Activity

OR with numbers

Customer	Allergies Group	Alcohol Group	BMI	BP	Physical Activity	Waist
AA001	1		1	1		1
AA002		1	1			1
BB001	1			1	1

Thanks!

Alex

Re: Qlik Sense: Separating comma separated values into columns by category

santhiqlik — Tue, 18 Dec 2018 06:25:27 GMT

Load
Customer,
If(Match([Areas for improvement],'Allergies'),1) as [Allergies Group],
If(Match([Areas for improvement],'Alcohol'),1) as [Alcohol Group],
If(Match([Areas for improvement],'BMI'),1) as [BMI]
If(Match([Areas for improvement],'BP'),1) as [
BP],
If(Match([Areas for improvement],'Physical Activity') ,1) as [Physical Activity],
If(Match([Areas for improvement],'Waist'),1) as Waist
Resident TableA

Re: Qlik Sense: Separating comma separated values into columns by category

marcus_sommer — Tue, 18 Dec 2018 07:52:08 GMT

With something like:

load Customer, trim(subfield([Areas for improvement], ',')) as Category from Source;

you could easily split the valuelist and creating a "normal" table-structure which is usually more suitable for the most kind of calculations/views as a crosstable which you mentioned as your target. If you really need a crosstable you could start with the above mentioned approach and apply on it a The-Generic-Load.

- Marcus

Re: Qlik Sense: Separating comma separated values into columns by category

Anonymous — Wed, 19 Dec 2018 22:27:57 GMT

Thanks for the responses, I still don't seem to be able to get this to work.

The data source is actually coming from an SQL search with many more columns and rows. Not sure if this is part of the issue?

Re: Qlik Sense: Separating comma separated values into columns by category

marcus_sommer — Thu, 20 Dec 2018 07:07:04 GMT

The source and also the size of the dataset is quite irrelevant for the functionality. The subfield-split is easy and fast even with large datasets. The generic load and the optional joining of them to a crosstable is instead a quite heavy transformation and will need some time especially by bigger datasets. But like already mentioned the creation of such a crosstable is often not necessary.

Depending on your data it might be useful not to apply this suggestion to your whole table else just to take a KEY (in your case probably the Customer) and the field with the valuelist - and both tables (your origin load and this new split one) are then just associated by the KEY within the datamodel because it avoids potential duplicating-issues of the records by any counting/summing calculations on it.

If you do not move on you should elaborate what didn't work in your case.

- Marcus