Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
aslam24196
Creator
Creator

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.

Please help me with this.

Thank you.

Labels (3)
1 Reply
marcus_sommer

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