Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

charliea
New Contributor

Separate lists of value in different row

I'm trying to transform my data to separate each element from a list in a different row as the example below.

Input data:

 

No, Serial Number

1, 20;23

2, 21-25

3, 6-10, 12

4, 3-5; 10-12

 

Objective:

No, Serial Number

1, 20

1, 23

2, 21

2, 22

2, 23

2, 24

2, 25

3, 6

3, 7

3, 8

3, 9

3, 10

3, 12

4, 3

4, 4

4, 5

4, 10

4, 11

4, 12

My problem is that there are two differents cases. Simple elements separated by a ';' and intervals with '-' that I have to complete.

I succeeded in separating the simple elements separated by the ';' with the subfield() function:

 

SubField("Serial Number", ';') as Number,

 

Similarly for the intervals with the iterno() function:

SubField("Serial Number", '-', 1) + iterno() -1 as Number

while(SubField("Serial Number", '-', 1) + iterno() <= SubField("Serial Number", '-', 2) +1);

But I do not find a solution to combine both to get the expected result.

Thanks for your help.

Charlie

1 Solution

Accepted Solutions

Re: Separate lists of value in different row

Hi,

maybe one solution could be:

QlikCommunity_Thread_258215_Pic1.JPG

LOAD No,

    SerNumMin+IterNo()-1 as SerialNumber

While SerNumMin+IterNo()-1 <= SerNumMax or IterNo()=1;

LOAD *,

    SubField(SerialNumber,'-',1) as SerNumMin,

    SubField(SerialNumber,'-',2) as SerNumMax;

LOAD *,

    SubField([Serial Number],';') as SerialNumber

INLINE [

    No, Serial Number

    1, 20;23

    2, 21-25

    3, 6-10; 12

    4, 3-5; 10-12

];

hope this helps

regards

Marco

3 Replies

Re: Separate lists of value in different row

Hi,

maybe one solution could be:

QlikCommunity_Thread_258215_Pic1.JPG

LOAD No,

    SerNumMin+IterNo()-1 as SerialNumber

While SerNumMin+IterNo()-1 <= SerNumMax or IterNo()=1;

LOAD *,

    SubField(SerialNumber,'-',1) as SerNumMin,

    SubField(SerialNumber,'-',2) as SerNumMax;

LOAD *,

    SubField([Serial Number],';') as SerialNumber

INLINE [

    No, Serial Number

    1, 20;23

    2, 21-25

    3, 6-10; 12

    4, 3-5; 10-12

];

hope this helps

regards

Marco

charliea
New Contributor

Re: Separate lists of value in different row

It works perfectly !

Thank you Marco.

Re: Separate lists of value in different row

you're welcome

regards

Marco