Separate lists of value in different row

[Anonymous]

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

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

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

[Anonymous]

It works perfectly !

Thank you Marco.

[MarcoWedel]

you're welcome

regards

Marco