Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
kaldubai
Creator
Creator

Challenging Case. Splitting a string into parts conditionally

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 +

1 Solution

Accepted Solutions
kaldubai
Creator
Creator
Author

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

View solution in original post

8 Replies
petter
Partner - Champion III
Partner - Champion III

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

kaldubai
Creator
Creator
Author

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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, ...,

...

'')))


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
petter
Partner - Champion III
Partner - Champion III

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Partner - Champion III
Partner - Champion III

>> 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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein