topic Re: How to explode a list of value ? in QlikView

How to explode a list of value ?

Wed, 19 Oct 2011 16:41:00 GMT

Good day,

I have a table that contains the following data:

- product

- category

But, in the field "category", I don't have a single category, but a list of categories, separated by a coma.

Example :

Product, Category

Coca-Cola, 'drink, soda, cola'

Fanta, 'drink, soda, orange'

Orange juice, 'drink, soft, orange'

...

I would like to have, as the result of my load, the following:

Product, Category

Coca-Cola, drink

Coca-Cola, soda

Coca-Cola, cola

Fanta, drink

Fanta, soda

Fanta, orange

Orange juice, drink

Orange juice, soft

Orange juice, orange

Nowaday, I use a script that counts the ", " and make loop to extract and concatenate a new table.

I wonder if there is another simplier way to make this ?

(something like an "explode" function)

Re: How to explode a list of value ?

its_anandrjs — Wed, 19 Oct 2011 16:49:32 GMT

Hi,

I suggest you have to use subfield it is so easy to load this type of data. If there is single symbol so use one subfield but if there is more symbol so use more than one subfield.

And you can do like this

Raw:

load * inline

[

Product, Category

Coca-Cola, 'drink,soda,cola'

Fanta, 'drink,soda,orange'

Orange juice, 'drink,soft,orange'

];

Data:

Load

Product,

Subfield(Category,',') as CategoryNew

Resident Raw;

Rgds

Anand

Re: How to explode a list of value ?

Wed, 19 Oct 2011 16:56:23 GMT

It can be done with subfield function. Attached the example. I used ";" instead of "," as the delimiter.

Re: How to explode a list of value ?

its_anandrjs — Wed, 19 Oct 2011 17:01:17 GMT

Hi,

And if there is more symbol so do like this

Raw:

load * inline

[

Product, Category

Coca-Cola, 'drink,soda,cola'

Fanta, 'drink,soda,orange'

Orange juice, 'drink,soft,orange'

];

Data:

Load

Product,

Subfield(Category,',',1) as CategoryNew

Resident Raw;

Concatenate

Load

Product,

Subfield(Category,',',2) as CategoryNew

Resident Raw;

Concatenate

Load

Product,

Subfield(Category,',',3) as CategoryNew

Resident Raw;

Drop table Raw;

Let me know or see the sample attached file.

Rgds

Anand

Re: How to explode a list of value ?

Wed, 19 Oct 2011 18:52:41 GMT

This is exactly the function I was looking for, thank you !

I don't understand what you mean by "if there is more symbols", so I don't understand what does your 2nd example.

I guess it's when I want to load only the X first categories ?