Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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.

Tags (1)
1 Solution

Accepted Solutions

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

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;

2 Replies

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

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

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

Great, thanks!

Community Browser