Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

splitting a dynamic ";" string into several columns?

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.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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;

Not applicable
Author

Great, thanks!