topic Re: How to number SubField results? in QlikView

How to number SubField results?

Thu, 08 Aug 2013 20:54:00 GMT

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.

Re: How to number SubField results?

hic — Fri, 09 Aug 2013 07:13:40 GMT

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

Re: How to number SubField results?

Fri, 09 Aug 2013 20:45:08 GMT

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