Performance choice for dynamic expressions

[gmenoutis]

I will be using code that might have slight syntactic errors, but I hope it will be sufficient to express my question.

If in some expression I use SUM(fieldname*5+2), the formula inside the parenthesis must obviously be evaluated for each fieldname value.

I guess this is the same in the case of SUM(if(fieldname<5,fieldname*3,fieldname/5)).

However, a variable has a constant value throughout all the data. So, one question is if the following: Will

SUM(if(varname<5,fieldnameA,fieldnameB))

be evaluated for each A-B combination, or is qlikview smart enough to evaluate the if condition once and then replace it by whichever field the condition leads to?

If the answer is "unfortunately, no", how about the following:

SUM(&(varname))

where in the script I have:

set varname=if(varname<5,'fieldnameA','fieldnameB')

Will this be evaluated once, as the formula text is not resolved from the start?

I hope, and believe, this must be "yes", since else how would qv know for what data combinations it would have to evaluate the final expression?

If I have been vague or there is any way to improve this post, please let me know and have a chance to make it right!

[Zhandos_Shotan]

Hi !

My answer is Yes.

Qlik evaluates IF condition for every record in table field without analysis what you coded and what you want to do .

Using IF condition check in measures is very expensive in terms of performance.

And your decision Sum($(varname)) is right BUT never forget one important thing:

/!\ Use '=' in variable before expression if want to calculate it first, before variable expanded:

let varname = '=if(varname<5,[fieldnameA],[fieldnameB])' ;

Using var without '=' works same as SUM(if(varname<5,fieldnameA,fieldnameB)) and no performance advantage.

Reference:

https://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/work-with-variables-...

[marcus_sommer]

It depends on your datamodel which way leads to the right results in the most performing way. If possible try to avoid constructs like: sum(if(condition, FIELD)) and replace it with: if(condition, sum(FIELD)) or even better with set analysis: sum({< FIELD = {'Condition'}>} FIELD) - see: A Primer on Set Analysis. The use of variables won't speed up any calculation because it is just a replacement of expression-text which will be evaluated before the calculations takes place.

- Marcus

[gmenoutis]

Marcus, your last sentence doesn't make sense to me - do you maybe intend to say "the use of variables won't slow down the calculation because...."?

[marcus_sommer]

A variable is just a replacement for anything else. This means it depends of the content of the variable if a calculation is fast/slow and correct or not. A bad and slow expression within a variable is just this.

- Marcus

[Zhandos_Shotan]

I do not want to dispute with Legend rank community member . But if variable is just replacement, i need explanation why example below works and how it works?

Fragment from qlik.help reference from my first reply:

Let vSales = 'Sum(Sales)' ;

Let vSales2 = '=Sum(Sales)';

Dim	$(vSales)	$(vSales2)
A	350	1560
B	470	1560
C	740	1560

Why vSales2 = 1560 for each row if its not 'Sum(total Sales)' ?

Also i did optimisations with some complex, messy expression whith multi level if conditions. Turn it into one row expression with precalculated multi level variables and it finally works several times faster.

May be i misunderstand something...

[marcus_sommer]

It's just a question to when the evaluation of the variable happens. By a variable with a starting equal sign the evaluation happens immediately and only the result of them is assigned to the variable and without equal sign the content will be evaluated on the place where it is used and during this run-time. It's much better explained here: The Little Equals Sign.

- Marcus