topic Re: splitting a dynamic ";" string into several columns? in QlikView

splitting a dynamic ";" string into several columns?

Wed, 30 Dec 2015 13:52:30 GMT

Hi,

Can you please tell me what is the most efficient way of getting a column which is a string consisting of several variables seperated by ";" (please see below) to split into several columns (one column for each variable between the ";"'s)

Needs to be done in the load script.

(Load script)

SQL select Date, ColumnToSplit from MyTable;

(Result Now)

Date: 01.01.2001

ColumnToSplit: 4682.50;2;1451481191401;165017;4689.47399965;true;

(Result Needs to Be)

Date: 01.01.2001

Column1: 4682.50

Column2: 2

Column3: 1451481191401

Column4: 165017

Column5: 4689.47399965

Column6: true

The lenght of the numbers can be different from a line to line and the last word also (sometimes its true, sometimes its false or sometimes its missing...

Thanks in advance for your help, much appretiated.

Re: splitting a dynamic ";" string into several columns?

sunny_talwar — Wed, 30 Dec 2015 15:11:11 GMT

May be this:

Table:

LOAD Date,

SubField(ColumnToSplit, ';', 1) as Column1,

SubField(ColumnToSplit, ';', 2) as Column2,

SubField(ColumnToSplit, ';', 3) as Column3,

SubField(ColumnToSplit, ';', 4) as Column4,

SubField(ColumnToSplit, ';', 5) as Column5,

SubField(ColumnToSplit, ';', 6) as Column6

SQL SELECT Date,

ColumnToSplit

FROM MyTable;

Re: splitting a dynamic ";" string into several columns?

Wed, 30 Dec 2015 15:35:43 GMT

Great, thanks!

Re: splitting a dynamic ";" string into several columns?

AsmJuv2004 — Wed, 24 Jul 2024 09:28:31 GMT

Hi @sunny_talwar

How do I make this dynamic. I need to do the same thing but my output column number will be different for each row.

Re: splitting a dynamic ";" string into several columns?

MarcoWedel — Tue, 30 Jul 2024 20:07:59 GMT

Hi,

maybe like this?

Generic
LOAD RecNo() as ID,
'Column'&IterNo(),
SubField(ColumnToSplit,';',IterNo())
Inline [
ColumnToSplit
4682.50;2;1451481191401;165017;4689.47399965;true;
4683.50;3;1451481191402;165018;4690.47399966;false;
4684.50;4;1451481191403;165019;4691.47399967;true;
4685.50;5;1451481191404;165020;4692.47399968;false;
] While IterNo()<=SubStringCount(ColumnToSplit,';')+1;