# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

Charlie

1 Solution

Accepted Solutions
MVP

## Re: Separate lists of value in different row

Hi,

maybe one solution could be:

```LOAD No,
SerNumMin+IterNo()-1 as SerialNumber
While SerNumMin+IterNo()-1 <= SerNumMax or IterNo()=1;
SubField(SerialNumber,'-',1) as SerNumMin,
SubField(SerialNumber,'-',2) as SerNumMax;
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
MVP

## Re: Separate lists of value in different row

Hi,

maybe one solution could be:

```LOAD No,
SerNumMin+IterNo()-1 as SerialNumber
While SerNumMin+IterNo()-1 <= SerNumMax or IterNo()=1;
SubField(SerialNumber,'-',1) as SerNumMin,
SubField(SerialNumber,'-',2) as SerNumMax;
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

New Contributor

## Re: Separate lists of value in different row

It works perfectly !

Thank you Marco.

MVP

you're welcome

regards

Marco