topic Re: Split one row to several in QlikView

Split one row to several

jindrichk — Fri, 13 Dec 2013 09:46:58 GMT

Hi,

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

The input is like this

ID	Data1	Data2	Data3
1	A	X85	15
2	B	D12	10
3	C	F40/E02	12/50
4	D	W16/R70	42/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 1	Header 2	Header 3	Header 4
1	A	X85	15
2	B	D12	10
3_1	C	F40	12
3_2	C	E02	50
4_1	D	W16	42
4_2	D	R70	55

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

Re: Split one row to several

Gysbert_Wassenaar — Fri, 13 Dec 2013 10:14:04 GMT

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.

Re: Split one row to several

tresB — Fri, 13 Dec 2013 10:30:33 GMT

A bit of tricks in the script. PFA

Re: Split one row to several

jindrichk — Fri, 13 Dec 2013 11:17:24 GMT

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