Challenging Case. Splitting a string into parts conditionally

[kaldubai]

hello everyone i have a problem need your help with it

i have a field that contains toppings added to a Pizza and the field looks like this

toppings:

RMADT0046;SFT0F0005;+060;060;+RMTOC0019;RMTOC0019

now this field actually contains both the extra toppings added and the default once

the extra once are prefixed with + sign

my question is

how to extract only the once that prefixed with a +

[petter]

TextBetween( toppings & ';' , '+' , ';' )

[kaldubai]

many thanks Perter for your replay

the formula you provided it only takes the first value but what if i have more than one sub string that is prefixed with +

[jonathandienst]

Its straightforward in the backend. Add this to your load after loading the main fact table (containing toppings):

Added:

LOAD *

WHERE added_topping like '+*'

;

LoAD toppings,

  SubField(toppings, ';') as added_topping

Resident <fact table name>;

Replace <fact table name> with the correct name of the resident table containing toppings. Use new field [added_toppings] in the front end expressions/dimensions.

[jonathandienst]

Its trickier in the front end because the number of toppings will not be known. You will need an unwieldy and awkward to debug expression like:

If(SubStringCount(toppings, '+') = 1, TextBetween(toppings & ';' , '+' , ';' ),

If(SubStringCount(toppings, '+') = 2, TextBetween('+'&SubString(toppings, '+', 1)&';' , '+' , ';' )&....

If(SubStringCount(toppings, '+') = 3, ...,

...

'')))

[petter]

TextBetween also takes a 4th parameter which tells which one of the matching toppings you want:

     TextBetween( toppings & ';' , '+' , ';' , 1 )         // picks the first match

     TextBetween( toppings & ';' , '+' , ';' , 2 )         // the second and so forth ... it doesn't matter if you pick one

                                                                            // that doesn't exists as it returns an empty string.

[kaldubai]

thanks johanthan for your response

the solution you suggested at first  finds the first plus then takes the whole string,then afterward it divides the string into sub string creating more rows  .

i actually i came up with a temporary solution which is somthing like your second post instead i used subfield function but as you mentioned the maximum number of toppings can be known but it is still not fixed

therefore i was wondering if there is any possibility to perform a for loop or while on a particular field to check the value part by part  

[kaldubai · Accepted solution]

well i have done the same thing but using subfield along the way with concat function , as for now the maximum number of substring count is 41 so i repeated the code about 45 in case the number exceeded 41 but what if it exceeded 45. that way ill have to check the maximum number every now and then and the code will get out of control

[jonathandienst]

>> string into sub string creating more rows

Don't do this in the main table and don't join it, My suggestion would have created a sidecar table with the additions, not extra rows in the fact/data table.