How to number SubField results?

[#1]

I need to number the SubField() function results so you can identify each part individually, see the code:

Source:
Load * Inline [
Id, Text
1, ‘part a,part b,partc’
2, ‘part a,part d,part e’
];
SubFields:
Load Id, 
SubField(Text,’,’) as SubText, 
magicfunction() as Order
Resident
                Source;

Expected result:

Id     SubText     Order

1     part a          1

1     part b          2

1     part c          3

2     part a          1

2     part d          2

2     part e          3

Right now I'm using RecNo() generating secuential number but gives me huge numbers for the last records.

Any idea what magicfunction() can be?

Thank you!

Enrique.

[hic · Accepted solution]

The magic function is IterNo(), but you need to use a While loop:

SubFields:

Load Id,

     SubField(Text,',',IterNo()) as SubText,

     IterNo() as Order

     Resident Source

               While IterNo() <= 1+Len(KeepChar(Text,','));

HIC

[#3]

Hernic thank you very much, it worked like a charm!