topic Re: Seperating values into seperate columns in QlikView

Seperating values into seperate columns

Sun, 03 Feb 2013 16:24:29 GMT

Hi,

I have another scripting challange! I have a single column from a database that is storing multiple categories seperated with a '|' (pipe). The number of categories a user can select is not limited, so some strings may have 1, 2 or 3 categories, but some may have potentially many more. I need to split out each substring into seprate colums.

ID	Categories
1	Car
2	Car\|Bike
3	Car\|Bike\|Boat
4	Car\|Bike\|Boat\|Plane

I require a table that looks like this

ID	1	2	3	4
1	Car
2	Car	Bike
3	Car	Bike	Boat
4	Car	Bike	Boat	Plane

Just to be clear, I do not know the number of columns that would need to be generated up front, so I cannot use simple string manipulation to extract the segments.

Thanks

Haider

Re: Seperating values into seperate columns

jagan — Sun, 03 Feb 2013 18:26:37 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Seperating values into seperate columns

CELAMBARASAN — Sun, 03 Feb 2013 18:57:40 GMT

Hi,

Little upgrade in Jagan code. Just added a code to join all the tables generated.

LET vStart = NoOfTables();

Test:

LOAD

ID,

If(Previous(ID) <> ID, 1, Peek(RowNo) + 1) AS RowNo,

Category;

LOAD

ID,

SubField(Categories, '|') AS Category

INLINe [

ID, Categories

1, Car

2, Car|Bike

3, Car|Bike|Boat

4, Car|Bike|Boat|Plane];

Split:

Generic

LOAD

Resident Test;

DROP TABLE Test;

LET vLoop = NoOfTables() - $(vStart) - 1;

//Rename first table name

RENAME Table Split.1 to Split;

FOR i = 1 to vLoop

LET vCurrentTable = 'Split.'& ($(i) + 1);

Left Join(Split)

LOAD

Resident

$(vCurrentTable);

DROP Table $(vCurrentTable);

Hope it helps

Celambarasan

Re: Seperating values into seperate columns

Sun, 03 Feb 2013 19:34:55 GMT

Amazing...Thank you to all that replied 🙂

Re: Seperating values into seperate columns

Sun, 03 Feb 2013 19:47:59 GMT

Hi.

I was doing the example file when you got the answer.

Maybe another way to do it.

Alessandro Furtado