Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Seperating values into seperate columns

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.
IDCategories
1Car
2Car|Bike
3Car|Bike|Boat
4Car|Bike|Boat|Plane
I require a table that looks like this
ID1234
1Car
2CarBike
3CarBikeBoat
4CarBikeBoatPlane
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
Tags (1)
1 Solution

Accepted Solutions

Re: Seperating values into seperate columns

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

NEXT

Hope it helps

Celambarasan

4 Replies
MVP
MVP

Re: Seperating values into seperate columns

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Seperating values into seperate columns

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

NEXT

Hope it helps

Celambarasan

Not applicable

Re: Seperating values into seperate columns

Amazing...Thank you to all that replied :-)

Not applicable

Re: Seperating values into seperate columns

Hi.

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

Maybe another way to do it.

Alessandro Furtado

Community Browser