Get the index of a subfield row

[Mancunia]

Hello,

When using Subfield() without a specified index, the cartesian product is generated and there is a new row for each subfield value.

The problem I'm having is that I can't identify which index each row relates to.

Are they guaranteed to be in order? Is there a function I can use to identify which subfield index the row refers to?

The source system I'm working with has up to 30 subfields in one field so bit of a nightmare.

Thank you.

[edwin]

Try this:

load
Player,
Project,
Project2,
SubStringCount(left(Project,Index(Project,Project2)),',')+1 as Position,
Index(Project,Project2) as Index;

//these two load statements are from the documentation for subfield---
LOAD DISTINCT
Player,
Project,
SubField(Project,',') as Project2;

Load * inline [
Player|Project
Neil|Music,OST
Jo|Music
Mike|Music,OST,Video
] (delimiter is '|');

Index gives you the position in number of chars of the subfield.  then you just count your delimiters before that position. 

[marcus_sommer]

You could combine subfield() with a while-loop to get this index from iterno(), for example like:

// load *, rowno() as RowNo, recno() as RecNo;
load iterno() as IterNo, Field, subfield(Field, YourDelimiter, iterno()) as FieldPart
from Source while iterno() <= substringcount(YourDelimiter) +1;

- Marcus

[Brett_Bleess]

You have been provided with two ideas, we would greatly appreciate it if you would close out the thread and mark the post(s) that helped by using the Accept as Solution button on the one(s) that did help you resolve things.  If you did something different, consider posting what you did and then after you post it, use the button to mark it as the solution to close the thread.

Regards,
Brett