Split Column into multiple Columns dynamically with Delimeter
Dear All,
I have Got data in the below Format.
Sr No ID
------- -------------------
1 1;-10;10;7.5;0;2;-11;4;2.5;1
2 1;-9;8;4;0;2;-9;8;9;1;1;-9;5;2;0;
Firstly,
I need to split the row value into multiple rows where the logic is after every fifth Delimiter its should be a new row:
Desired Result :
Sr No ID
--------- -------------------
1 1;-10;10;7.5;0
1 2;-11;4;2.5;1
2 1;-9;8;4;0
2 2;-9;8;9;1
2 1;-9;5;2;0
As seen above, the Number of rows to be split will vary for every SR No.
After this I need to Split ID field into 5 Different fields but I can do it using SubField() But I am not sure how to split a row into multiple cells for the above logic.
load *, mid(ID, Start, End) as Value; load *, iterno() as IterNo, if(iterno() = 1, 1, index(ID, ';', (iterno() - 1) * 5) + 1) as Start, index(ID, ';', rangemin(substringcount(ID, ';'), iterno() * 5)) - 1 as End while iterno() <= ceil(substringcount(ID, ';') / 5); load * inline [ Sr No, ID 1, 1;-10;10;7.5;0;2;-11;4;2.5;1 2, 1;-9;8;4;0;2;-9;8;9;1;1;-9;5;2;0; ];
An alternatively would be to use subfield(ID, ';') to split each value into an own record and also adding a 5 record-key and aggregating them again, like:
t1: load *, [Sr No] & '|' & ceil(Counter / 5) as Key, rowno() as RowNo; load *, subfield(ID, ';') as Value, if([Sr No] <> peek('Sr No'), 1, rangesum(peek('Counter'), 1)) as Counter; load * inline [ Sr No, ID 1, 1;-10;10;7.5;0;2;-11;4;2.5;1 2, 1;-9;8;4;0;2;-9;8;9;1;1;-9;5;2;0; ];
load [Sr No], concat(Value, ';', RowNo) as Value, rowno() as RowNo resident t1 where len(Value) group by Key, [Sr No];
