Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gmenoutis
Partner - Creator II
Partner - Creator II

Performance choice for dynamic expressions

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!

Labels (1)
6 Replies
Zhandos_Shotan
Partner - Creator II
Partner - Creator II

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

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
Partner - Creator II
Partner - Creator II

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)
A3501560
B4701560
C7401560

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