Seperating values into seperate columns

[#1]

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

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[CELAMBARASAN · Accepted solution]

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]

Amazing...Thank you to all that replied 🙂

[#5]

Hi.

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

Maybe another way to do it.

Alessandro Furtado