Concatenate String Variables to create Expression

Mon, 18 May 2015 00:18:28 GMT

I have a project where I need 48 variations of an expression, so i'm trying to find a way to use variables as much as possible.

The chart will display average orders placed per person in 6 time increments (30/60/90 days/6 mth/12mth) before and after a point in time based on user selection via button that sets 4 variables (in blue below). The final expression looks something like this:

Count(DISTINCT {<OccurrenceFlagField = {'CriteriaPrefixTimeIncrement'}>} ORDER_ID) / Count(DISTINCT {<PersonFlagField = {PersonCriteria}>} PERSON_ID)

For each possible event (registration date, first online order date, first any coupon use date, first specific coupon use date), I have buttons that set variables corresponding to the text in blue above:

OccurrenceFlagField - a field on the orders table for each event that contains a value indicating whether the order falls into one of the 12 date ranges for the consumer (ie. 'RegDt-30~FirstOnl+6m' indicates the order was placed within 30 days before registration date and within 6 months after first online order was placed)

CriteriaPrefix - The string value in the flag field that corresponds to the selected event (i.e. 'RegDt' or 'FirstOnl')

PersonFlagField - the field which indicates the person qualifies to be counted in the expression (i.e. RegisteredFlg or OnlineOrderCount)

PersonCriteria - indication criteria for the set analysis (i.e. '1', '>0')

I have the above expression in a variable that concatenates all the text strings together with the 4 variables and it works, with TimeIncrement hard-coded. But for the sake of maintenance I'd rather not have 12 versions of the expression variable in my design (I WILL forget to update them all when changes are made). I tried to break up the expression into parts before and after the time increment, and just hard code the time increment in the expression on the chart, but it won't calculate that way, although when i hover over the expression header, the expression looks the same as it originally did when i had one variable with the time increment hard-coded.

VariableA = Count(DISTINCT {<OccurrenceFlagField = {'CriteriaPrefix

VariableB = '}>} ORDER_ID) / Count(DISTINCT {<PersonFlagField = {PersonCriteria}>} PERSON_ID)

Expression: =$(VariableA) & '-12m' & $(VariableB)

I'm working on a sample doc to better represent what I'm looking to do, and will attach it as soon as I can. Any ideas are appreciated.

Re: Concatenate String Variables to create Expression

ToniKautto — Mon, 18 May 2015 00:59:13 GMT

Just a guess here.

You need to expand the variables as text. The way to enforce that is to single quote the dollar expansion. There is no need to concatenate the expansion as such, just put all of them inside the same quote.

='$(var1)$(var2)$(var3)'

An of course since you are creating field names and values, make sure there are no unfortunate blank spaces in the expanded result. For example the two expansions below will not be the same result

='$(var1)$(var2)$(var3)'

='$(var1) $(var2)$(var3)'

Re: Concatenate String Variables to create Expression

Mon, 18 May 2015 02:43:32 GMT

Your answer got me almost there, the final solution just eliminates the quotes

=$(vExpression_a)$(='-6m')$(vExpression_b)

Thanks!!

topic Re: Concatenate String Variables to create Expression in QlikView

Concatenate String Variables to create Expression

Re: Concatenate String Variables to create Expression

Re: Concatenate String Variables to create Expression