Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
It works perfectly !
Thank you Marco.
you're welcome
regards
Marco