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?
Ok, there's a fourfold nested if in eUGAssessmentScore_0. A bit more complicated than adding up a bunch of sums. I think those nested if's account for a good part of the performance problem you experience. I don't see a way around that right now. Perhaps it's possible to precalculate some things in the script.
The use of powers in my expressions limit somewhat my ability to do the calculation in the load (the square root of one plus the square root of two does not equal the square root of three). Likewise, I cannot compare the population sizes to the thresholds in the load because 1) the thresholds can change by a control in the application and 2) the population size is dependent on any filters applied.
However, whilst eUGAssessmentScore_0 is obviously going to slow things down, I am not looking for ways to make eUGAssessmentScore_0 more efficient. What I have been asking for is a way to ensure that eUGAssessmentScore_0 only evaluates once per row, rather than three times as is currently the case in eUGAssessmentGrade_0 .
