For the hardcore techies.. Qlik's memory referencing

[vkish16161]

All,

I have a question for you:

Imagine that there are 2 calculations namely X and Y which are very complex in nature. Also imagine that their Set Analysis flags come from the farthest table from the fact table and Qlik's logic has to pass through multiple 'hoops' before evaluating the filter.

Now..

Imagine that there are 2 charts with the same dimension but in 1 chart the measure is of the form (X-Y) / X  and  in another chart, the measure is (1-Y/X) . Mathematically both equations are the same but how will Qlik evaluate this? Will Qlik 'think' these are 2 different expressions and render them separately? Or does it happen as one?

Also, In the first chart, Am I correct in assuming that X is evaluated twice and Y is evaluated once.

while in the 2nd chart, X and Y are evaluated once.

Which calculation will render faster or will they be the same at least theoretically?

Would variablizing the calculation help?

Also, remember that now the calculation engine has changed from QlikView Engine to QIX Engine (Column based). So will this make any difference?

Thanks!

rwunderlich‌hic‌robert_mika‌

[vkish16161]

Rob,

Yeah that is what I'm currently doing. But there's only a slight improvement in performance.

Sorry about thinking that you wrote a book Rob. (However, I feel that you should. Lot of developers will benefit from your knowledge.

Yes for Boolean flags, how would the performance be by using 0 and 1 vs true(),false().

Also if I put 0 and -1 as strings/numbers in the flag, will qlik interpret this as true/ false or as just strings.

Also, in all of our charts, for dimensions, we're using variables. (Dynamic Dimensions to be precise) Here's one:

set vcomparison        = if(GetSelectedCount(Comparison)>1,'prim_Sanofi_Novo_access_Reg',

                                if(getfieldselections(Comparison)='Sanofi vs. Novo','prim_Sanofi_Novo_access_Reg',

                                    if(getfieldselections(Comparison)='Lantus vs. Bassaglar','prim_Lan_Bslg_access_Reg',

                                        if(getfieldselections(Comparison)='Toujeo vs.Tresiba','prim_Tjo_Trs_access_Reg','prim_Sanofi_Novo_access_Reg'))));

My solution:

Pick(Alt(Match(Comparison,'Sanofi vs. Novo','Lantus vs. Bassaglar','Toujeo vs. Tresiba'),4)

,[prim_Sanofi_Novo_access_Reg],[prim_Lan_Bslg_access_Reg],[prim_Tjo_Trs_access_Reg],[prim_Sanofi_Novo_access_Reg])

I'm using 'Alt' to avoid Multiple selection  and also importantly default selection.

Is this process comparable to an else clause in if? if this is so, I can replace our nested if's with this.

Also, are there any shortcomings with this approach? Is this any performance efficient at all? If you could say so, I'll get this implemented across the board.

Thanks in advance!

marcus_sommer‌ rwunderlich‌

[marcus_sommer]

Strings will always need more resources than numerics. I haven't never checked differences within the performance between 0/1 or false()/true() - usually I use 0/1 directly and would assume that it would be more performant than the boolean-functions because the booleans are dual-values. Just put false() and num(false()) with a textbox and you will see what is meant. Using 0/1 has the further advantage of be usable as a multiplicator like: sum(value) * FLAG.

To your second query: (nested) if-loops should be avoided when ever possible. Your approach with pick(match()) is easier to develop and to maintain and more performant as the if-loops.

- Marcus

[vkish16161]

Marcus,

Thanks for your reply on this. Maybe you're referring to this blogpost: On Boolean Fields and Functions

by Henric. But contrary to your suggestion, Henric said Boolean functions are faster in flags. What you said about multiplicators are true though but they won't work for AVG etc. (This is also covered in the article).

For my second query:

Just to clarify, I'm using Alt function for default selection when no selection is made or multiple selections are made.

Is this comparable to the else clause?

Just curious.

Once again, thanks for your input on this

[marcus_sommer]

Of course I know this posting but I haven't referred to it in my answer. I couldn't see where my suggestions are contrary to the suggestions from HIC. If the performance point of view by applying conditions to expression without flags and with different kinds of flags are really important for you - you should try it out by creating one ore several dummy datasets with at least 100 millions records - if you really do it, please made it systematically and post your results with all scripts and applications here in the community.

The alt() functions isn't really to consider as else-part of an if-loop else as a default-value if all other parameters or branches are failing respectively not returning a numeric value.

- Marcus

[vkish16161]

Marcus,

Sorry for the delayed response.

I did create a dataset. I tried with and without boolean flags but there was no way to accurately quantify performance. We're trying to investigate other ways of quantifying performance.

For 2nd question. - I have used Alt as an Else clause.

(Only useful in some scenarios but our entire app is using field functions - getfieldselections etc. )

My goal was to replace IF with Pick, Alt, Match - Here's proof:

Field_Name:

[ Channel Description]

Commercial

Health Exchange

Ltc

Managed Medicaid

Medicaid

Medicare

In a textbox if you use this expression:

alt(Match([Channel Description],'Commercial','Health Exchange','Ltc')+1,1)

The output for the above expression is {1,2,3,4} - 1 being default (No selections, Multiple Selections, Wrong Selection)

This output i.e. {1,2,3,4} can be used by Pick function.

Please give your thoughts on this..

marcus_sommer‌ rwunderlich‌

[marcus_sommer · Accepted solution]

As I mentioned before it's not very easy to measure the performance of different ways to do a thing in QlikView. But it could be done by using the mem-files like described in the above link and some smaller checks could be even done without this just by using the document properties within the tab sheets - the column time will show the calculation time from the first opening. Important by this is to disable the qlik caching or to close the application each time and opening them again.

Therefore it will be easier in your case just to increase the amount of data until you see a real difference between the different calculations - and you should use a pivot for it with multiple dimensions to give qlik something to calculate.

Your second point isn't really clear for me - maybe you could get your wanted results rather with a getselectedcount() or a count(distinct FIELD) and one or two if-loops. Of course if-loops should be avoided if possible especially if they are calculated multiple times within charts but by a single calculations within a textbox you could be a bit pragmatic and just use the if-loop for simplicity instead of spending much efforts to implement a different solution.

- Marcus

[rwunderlich]

If you can't quantify performance, than I would say it's "good".

-Rob

[vkish16161]

Thanks for the help all. Our app is going to production this week and my client was reasonably happy with the performance. So I think i'll say performance is "good"