topic Re: Challenging Case. Splitting a string into parts conditionally in QlikView

Challenging Case. Splitting a string into parts conditionally

kaldubai — Thu, 19 Jan 2017 09:24:23 GMT

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 +

Re: Challenging Case. Splitting a string into parts conditionally

petter — Thu, 19 Jan 2017 14:52:15 GMT

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

Re: Challenging Case. Splitting a string into parts conditionally

kaldubai — Wed, 25 Jan 2017 07:41:11 GMT

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 +

Re: Challenging Case. Splitting a string into parts conditionally

jonathandienst — Wed, 25 Jan 2017 08:04:47 GMT

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.

Re: Challenging Case. Splitting a string into parts conditionally

jonathandienst — Wed, 25 Jan 2017 08:12:14 GMT

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

...

'')))

Re: Challenging Case. Splitting a string into parts conditionally

petter — Wed, 25 Jan 2017 09:06:02 GMT

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.

Re: Challenging Case. Splitting a string into parts conditionally

kaldubai — Wed, 25 Jan 2017 09:11:21 GMT

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

Re: Challenging Case. Splitting a string into parts conditionally

kaldubai — Wed, 25 Jan 2017 09:17:54 GMT

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

Re: Challenging Case. Splitting a string into parts conditionally

jonathandienst — Wed, 25 Jan 2017 13:51:14 GMT

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