Iteration on multiple value field creating multiple rows

[#1]

This is sort of interesting, I think. [:P]

I make a load where I have one field that may contain multiple values with a seperator.

LOAD customer ID, customerTags (multi field), percentage, x, y, z ..

What I need to do is to create a new parameter with the percentage split on the count. I realize that I need to perform some iteration and that this will give me as many rows as values in the multi value field.

12, market1, 1.0, x, y, z ..

13, market1, 0.5, x, y, z ..

13, market2, 0.5, x, y, z ..

If I have one value in the multi value field, I place 1 in the percentage field, If I have two values, I will have to create 2 loads of the row, split the multi value field into one on each post and place 0.5 in each row's percentage field. With 3 values in the multi value field has to create 3 rows with the splitted values in each row and 0.33 in the percentage fields. Do you follow me? [8-|]

So in the my end application I then may select a customer and have a percentage value connected to a market to calculate with. Perhaps this is basic?

[Miguel_Angel_Baeyens]

Hello,

You may use

SubField(Field, 'separator')

function, should your values are separated uniformly. It will create a new record for each value stored in field. Is that what you mean?

[#3]

Sure Miguel, you are totally right. I missed that because I fully had my attention on the percentage calculation. Thank you.

Anyhow, how may I count how many subfields I have on each record? Couldn't find any good function for that.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Using

Substringcount(Field, 'separator') + 1 AS FieldsValuesNo

you will know that are that number of values for each Field plus one.

1;2;3

Will give you 2, so you have 3 values.

[#5]

Thank's alot. So simple but still elegant. 🙂