Subfield + AutoNumber(RowNo() or similar

[Alessandro87]

Hello,

I have a field that is composed like this:

/123-abc-456/321-bca-654/789-cvb520/489-gty-663

what I need is to extract each value so subfield(field, '/') as field

but I have to assign an incremental index to each values, so at the end I will have:

field 123-abc-456 depth 1

field 321-bca-654 depth 2

etc...

Any ideas on how to do it? (of course the field is random, can't use if(...then...

Many thanks!

[marcus_sommer]

It could be simply reached with a while-loop and subfield(), for example with:

load *, subfield(Field, '/', iterno()) as X, iterno() as Y
from Source while iterno() <= substringcount(Field, '/') + 1;

[rwunderlich]

You can use AutoNumber as you suggested in a preceding load.

Data:
LOAD *,
  AutoNumber(field, Key) as depth
  Where len(field) > 0 
; 
LOAD *,
  RecNo() as Key,
  Trim(SubField(raw,'/')) as field
Inline [
raw
/123-abc-456/321-bca-654/789-cvb520/489-gty-663
/788-xyz-456/999-bca-654/1111-cvb520/22-gty-663/rob-xyz
]; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Alessandro87]

It worked, thanks.

Now I have this situation, and I have to extract only distinct y,x value that match this condition:

max value of z

every values is created randonly by the BE, so can't use any if(x=to...then...) or inline.

I have to extract it in the load editor script while loading data from the DB.

Thanks!

[marcus_sommer · Accepted solution]

Maybe with a self-join, like:

inner join(YourTable) load X, max(Z) as Z resident YourTable group by X;

[Alessandro87]

Worked! Thank you!