Using one expression to determine which other expression to execute
Hi QlikView Community,
I have an expression stored as a string literal in a variable (eExpressionA) that equates to a value of 1, 2 or 3 depending on whether a sum function reaches certain thresholds. What I want to do is to use this expression to determine which of three other expressions (also stored in variables: eExpression1, eExpression2, eExpression3) to execute in each row of a chart.
One way i can achieve this is with a simple Pick statement, e.g.:
=Pick($(eExpressionA)
,$(eExpression1)
,$(eExpression2)
,$(eExpression3)
)
However, the way QlikView seems to work, this will execute all three expressions for each row and then evaluate the pick statement to select the appropriate answer. This gives me the correct answer for each row but makes the application unworkably slow.
Therefore, I thought I could get around this by putting the Pick within the $(). For instance:
However, when I do this, eExpressionA is evaluated only once and ignores the dimensions in the table. Therefore, rather than choosing the expression to execute based on the subtotal of the sum in eExpressionA for each row in the dimensions, it picks the value based on the total for the whole dataset and then uses this for every row.
Is there a way that I can construct this expression so that QlikView will only execute one of the three expressions in each row in the chart?
What Qlikview will do is expand all the $(...) parts. It depends on the expressions inside those what actually is calculated or not. Afaik the pick function does not evaluate each option, only the one that is picked. But the dollar expansions happen before the pick function itself is evaluated.
What the expressions are shouldn’t really matter. Assume they are ’Sum(A)’, ’Sum(B)’ and ‘Sum(C)’. I can remove the $()s from around the eExpressiion1, eExpression2... variables so that all Pick is doing is selecting one of three strings rather than the outcomes of three expressions, but then I have to enclose the Pick statement in a $() in order to evaluate the expression contained in the chosen string (as in my second option). The problem is that as soon as I nest $(eExpressionA) within another $(), when $(eExpressionA) evaluates it ignores the dimensions in the chart.
I guess my question comes down to how can I make eExpressionA dimension-specific when it is evaluated within another $()?
Matk, I am not really following what you are trying to do. Any way you can attach a file with an example? From what I am seeing here your best bet would be this:
=Pick($(eExpressionA)
,SUM(A)
,SUM(B)
,SUM(C)
)
That would only cause the picked formula to calculate and it would also work across each dimension in the chart.
Or if you define each variable as a string then it will only fill in the formula with the dollar sign expansion rather than execute it.
$(eExpression1)='SUM(A)'
$(eExpression2)='SUM(B)'
, etc
I may be missing something and I'm sorry if I am but is it required to have the dollar sign expansion around the pick function? Perhaps if you attach a file I can help formulate a way around it.
I do have my expressions (1,2 and 3) stored in variables as strings. I have attached an example that hopefully helps explain things better.
The form of the expression in Column 1 works and produces the correct answer but, in my real world application, is unworkably slow as eExpression1, eExpression2 and eExpression3 evaluate for every row. In Column 2 I have removed the $() from the variable names, the result being that what is returned is just the string contained in the relevant variable based on the Pick(). As I understand it, to then convert these strings into Sum() expressions, I need to enclose them in a $(), which is what I have done in Column 3. However, when I do this, $(eExpressionA) evaluates to the same value for each row (3), rather than being conditional on the dimension.
My hope was to be able to recreate the outcome in Column A, but without the need to evaluate Sum(A), Sum(B) and Sum(C) in every row. If I have understood you and Gysbert correctly, it is not the Pick() that is resulting in the tripling in the number of expressions being evaluated but the inclusion of the $()s around each variable in the Pick(). (I think I remember reading that QlikView evaluates If() statements by evaluating both the then and else parts and returning the relevant answer dependent on the first term, and I have assumed that Pick() would work in the same way.)
If this is the case, I might be able to rework my expressions so that I include the expressions themselves rather than variables in the Pick() statement as you suggested (and I have done in Column 4). I do find this odd, however, as, in my mind, Column 1 and Column 4 should be equivalent?
Re-reading it now, the responder seems to suggest that Pick() will be evaluated in the same way. If this is right, I am still without a solution to my problem.
My guess it that it's your eExpressionA that's making things slow. That will be evaluated for each row and there are two nested if statements with a sum expression in each.
Try for example replacing your eExpresion1, eExpresion2 and eExpresion3 with just fixed numbers like 10, 20 and 30. Does that solve the performance problem? My guess would be no. Then try another test by replacing eExpressionA with a fixed value and keep the original eExpresion1, eExpresion2 and eExpresion3. How does that impact performance?
If I replace eExpression1, eExpression2 and eExpression3 with constants then the performance is fine. If I change eExpressionA so that it keeps the nested ifs and sums but always evaluates to 1 and then change just eExpression2 and eExpression3 to constants then it is still acceptable. If, however, I replace eExpressionA with a constant (1) but keep eExpression1, eExpression2 and eExpression3 as they are, performance is no good.
Having three sum expressions in eExpressionA slows things down a bit, but nowhere near as much as having to evaluate eExpression1, eExpression2 and eExpression3 for every row. In my application, eExpression1 might, for example, have up to 40 sum statements and the Pick statement used to select the expression could equate to up to 12 different numbers. Therefore, no, it is not the three sums in eExpressionA that it is slowing it down too much, it is the 40 x 12 = 480 sums in eExpression1, eExpression2, eExpression3... and there are 21 similar groups of expressions in the chart.
This is why I want to limit the number of eExpressions that gets evaluated to just one per row so that rather than having to calculate 3 + 480 sums, it only needs to calculate 3 + 40. To do this, I need to find out if there is a way that means $(eExpressionA) takes into account the dimensions when it is evaluated from within another $() or some other way of forming my chart expression so that only one of the expressions stored in the numbered variables gets called per row.
Focussing on what expressions are contained in the variables rather than what is in the chart expression itself is, I fear, a red herring.
Well, the chart expression itself is what's constructed out of what's contained in the variables. If we can't focus on that then there's nothing to talk about anymore. OK, eExpressionA is not the problem. So either you accept that pick always evaluates all options (since we can't prove otherwise) and give up on it. Or you can try to give more information about the other eExpression variables. Are there if statements in them? Or is it just sum(A)+sum(B)+sum(P)+sum(X) etc? An example would help. Could you create a small demo that demonstrates the problem: Preparing examples for Upload - Reduction and Data Scrambling
And I understand you have 21 of those pick expressions in your chart? That kinda sounds like you're trying to use Qlikview as a spreadsheet tool. Not exactly what it was designed for. Is the performance of a single one of those pick expressions bad or is that with all 21?
I have 21 performance indicators that are being calculated as either the number of standard deviations or the absolute difference between an internal proportion [sum(SCORE)/sum(POP)] and a benchmark proportion [sum(BENCHMARK)/sum(POP)]. These (standardised) differences are then compared to minimum thresholds to assign a score of between 1 and 5 for each PI. However, for each of these there are also minimum population size criteria and so the field used to determine SCORE, BENCHMARK and POP will depend on the size of the population. (Within my data table there are between three and 12 columns for SCORE, for instance, for each PI corresponding to different levels of aggregation.) - This is where the pick() comes in.
Once I have the 21 individual scores, these are weighted and combined into two indices that are plotted on an x-y scatter plot. At the end of the day, I have just two expressions that are calculating these two coordinates. However, they are very long and so I have simplified the issue here. Whilst each expression contains a lot of components, each component is broadly similar to the others. Therefore, if I can solve the issue for one I can solve it for all.
I have attached an example of how the 1-5 grade is being calculated for one of these PIs. The variable eUGAssessmentGrade_0 is the one that I need help with. This is in the form:
=Pick($(eExpressionA)
,$(eExpression1)
,$(eExpression2)
,$(eExpression3)
)
and so is calculating the grade (eUGAssessmentScore_0) at all three levels of aggregation and then picking the grade that corresponds to the argument in pick().
I am fully aware that most of the individual elements within this expressions require QV to undertake a fair amount of computation. Each in themselves is fine, but when they all need to calculate at once I run into issues. There is nothing that I can do about that. What I am trying to accomplish is to minimise the amount of computation needed by finding a way to structure these expressions so that the two unnecessary options out of eExpression1, eExpression2 and eExpression3 are not being evaluated for every row. If I can achieve this, the performance is acceptable.
As it is currently defined, all three expressions are evaluated. Using pick to return strings and then evaluating the string reduces the number of expressions evaluated to one, but then I cannot find a way to make my pick argument dimension-specific. Perhaps there is a way that I am not seeing? Perhaps pick is not the solution and there is another formulation I could try?
