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: 
jindrichk
Contributor II
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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

A bit of tricks in the script. PFA

View solution in original post

3 Replies
Gysbert_Wassenaar

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
tresesco
MVP
MVP

A bit of tricks in the script. PFA

jindrichk
Contributor II
Contributor II
Author

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