Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Please help me with this.
Thank you.
You may try something like this:
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];
drop tables t1;
- Marcus