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.
jindrichk
New Contributor II

Split one row to several

Hi,

I would like to split one row to several based on a coilumn. Here is the example

The input is like this

IDData1Data2Data3
1AX8515
2BD1210
3CF40/E0212/50
4DW16/R7042/55

And I need to have new table where rows with slash in Data2 column will be splited to several rows. So output should be like this

Header 1Header 2Header 3Header 4
1AX8515
2BD1210
3_1CF4012
3_2CE0250
4_1DW1642
4_2DR7055

Currently I'm using the FOR statement to go through all the lines of original table and adding new line when slash is found. But this is very slow. Isn't there any function which can help me to do it much faster?

Thanks in advance,

Jindra

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Split one row to several

A bit of tricks in the script. PFA

3 Replies

Re: Split one row to several

Try something like this:

Input:

load

  text(if(SubStringCount(Data3,'/'), ID & '_' & dummy,ID)) as ID

  , Data1

  , Data2

  , SubField(Data3,'/',dummy) as Data3;

load * ,  AutoNumber(Data2,ID) as dummy;

load  ID,  Data1, subfield(Data2,'/') as Data2, Data3 INLINE [

    ID, Data1, Data2, Data3

    1, A, X85, 15

    2, B, D12, 10

    3, C, F40/E02, 12/50

    4, D, W16/R70, 42/55

];

You can replace the inline load with something that loads data from a database or file.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Split one row to several

A bit of tricks in the script. PFA

jindrichk
New Contributor II

Re: Split one row to several

Thanks to both of you for such quick response. Both of answers work great, tresesco solution is also faster.

Community Browser