Summary: I offer some tips for writing better performing and easier to maintain syntax when using the Qlik If() function.
The Qlik If() function is very powerful and frequently appears in Qlik Sense and QlikView apps.
Expressions using multiple If() functions can easily get out of hand and become difficult to maintain or debug, as well as poor performers.
In this post I’ll offer some advice on avoiding If() pitfalls and tips to write easier to understand expressions.
The Qlik syntax diagram for the If function is:
if(condition , then [, else])
That’s perfectly clear to most people, but I prefer to think of it more like:
if(condition , true result [, false result])
Tip#1: If() does not short circuit.
Both the true & false branches are calculated even when only one is possibly true. For example:
If(Only(Currency = 'LC', Sum(Sales), Sum ([Sales LC])
In this case both Sum() expressions will be calculated even though only one value will be utilized. In most cases this behavior is not of concern and in many applications will perform very well. However, a nested If() with many possible branches or a large data set may perform poorly.
For more on the short circuit issue see “How to Choose an Expression“.
Tip#2: Use indentation sparingly.
The true or false result may be an additional, “nested” If(), which is where we start to see some ugly syntax. Following traditional programming conventions many people automatically indent the nested if like this:
If(Sum(Sales) > 100000, 'Large',
If(Sum(Sales) > 75000, 'Med',
If(Sum(Sales) > 50000, 'Demi', 'Small')
)
)
Essentially, the expression above classifies into one of four values. I don’t think indentation adds to the readability and indentation will lead you into “tab hell” when you get many possibilities. I prefer to write this expression as:
If(Sum(Sales) > 100000, 'Large'
,If(Sum(Sales) > 75000, 'Med'
,If(Sum(Sales) > 50000, 'Demi'
,'Small'
)))
No indentation, all the closing right parens collected on one line at the end. Makes it very easy in the expression editor to see that you have the right number of parens.
The leading (vs trailing) commas are my personal preference. This make it easier to comment out logic and in my view, the comma belongs to the If that follows it, not the preceding If.
I think the above syntax makes it very easy to understand that I am choosing one of four results, and what the rule is for each result. Syntactically each If() is the else parameter of the preceding If(). I don’t think of the Ifs as “combined”, rather as “sequential”.
Do indent when you are using If() as the then parameter, as shown in Tip#4 below.
Tip#3: Simplify by testing from high to low.
The business rule that created this sample expression may have been stated to the Qlik developer like this:
“Classify sales of 0 to 50 000 as “Small”, 50 001 to 75 000 as “Demi”, 75 001 to 100 000 as “Med” and above 100 000 as “Large”.
The developer may faithfully translate the requirement into this expression.
If(Sum(Sales) > 0 and sum(Sales) <= 50000, 'Small'
,If(Sum(Sales) > 50000 and Sum(Sales) <= 75000, 'Demi',
,If(Sum(Sales) > 75000 and <= 100000, 'Med'
,'Large'
)))
This returns the correct result. Testing from low to high values forces the use of “and” which makes the expression more complex than necessary and potentially slower to execute. In my experience, testing from high to low, as in the Tip#2 example, yields a cleaner syntax.
Tip#4: Use “and” when you mean and.
Here’s a sample expression requirement:
When Sales > 1000 and Region=’US’, it’s “Mega US”. When Sales > 750 and Region = ‘UK’, it’s “Mega UK”. Otherwise it’s “General”.
I have seen this written as:
If(Sum(Sales) > 1000,
If(Region = 'US', 'Mega US'),
If(Sum(Sales) > 750,
If(Region = 'UK', 'Mega UK'),
'General')
While the “and” requirement may be satisfied with a then-if nesting, I find it clearer with the “and” keyword.
If(Sum(Sales) > 1000 and Region = 'US', 'Mega US'
,If(Sum(Sales) > 750 and Region = 'UK', 'Mega UK'
,'General'
))
What if the requirement for both US & UK were 1000? You could argue that this is clear case for nesting in that there is a shared condition and perhaps it would be a good practice to not repeat ourselves on the Sum(Sales).
If(Sum(Sales) > 1000,
If(Region = 'US', 'Mega US',
If(Region = 'UK', 'Mega UK'), 'General'),
'General')
Notice we needed to repeat the ‘General’ result to cover the null case. So it’s not super clean, but it may be worth it to not repeat the sum(Sales) calculation. Generally I find the performance difference between “and” and “nested if” to be insignificant and tend to favor whatever is the clearer syntax for the given requirement.
What about Pick(Match())?
I’ve heard it occasionally claimed that a Pick/Match combination will run faster than a nested If. The expression might look like this:
Pick(
Match(
-1
,Region= 'US' and Sum(Sales) > 1000
,Region= 'UK' and Sum(Sales) > 1000
, -1
)
,'Mega US', 'Mega UK','General')
In my own testing and reading I’ve never found any performance advantage to Pick/Match. That said, sometimes the syntax is appealing.
One thing I don’t like about Pick/Match is the distance between the condition list and the result list. It’s fairly easy to get the lists mis-aligned as the expression grows.
I wish Qlik had a Switch type function like:
Switch (
condition1 : result1
[,condition2 : result2, ...]
[: defaultResult]
)
Tip#5: Simplify by using Column(n) or Measure Name
If your if() refers to something that has already been calculated in the chart, you can use the Column(n) function to refer to the value of a measure/expression column. For example, in a color expression:
If(Column(2) > 0, Green(), Red())
This can be much neater than repeating the expression text and typically runs faster as well.
If you are on Qlik Sense May 2021 you can use Master Measure names in the expression like:
If([Total Sales] > 0, Green(), Red())
[Total Sales] need not be a measure in this chart.
Both QlikView and Qlik Sense also allow you to reference the Label of a measure/expression column in the chart. In most versions the syntax checker will declare this an error even though it calculates correctly. I tend to avoid the label technique due to this confusion.
Tip#6: Don’t use If() as a chart filter
Use If when you want to dynamically select from two or more alternatives. If should not be used simply to filter data like this:
Sum(If(Region = 'EU' and CYTDFlag = 1, Sales)
Filtering is best done with Set Analysis. The same expression written with a Set:
Sum({<Region={'EU'}, CYTDFlag={1}>} Sales)
Set Analysis is much faster than If. If you are new to Set Analysis, you might initially find the syntax more challenging than If. But SA is much more powerful than If and well worth mastering.
Tip#7: Consider the other conditional functions.
Alt() and Coalesce() can be a more compact and elegant approach to testing for nulls. Instead of:
If(IsNull(SalesRep), Manager, SalesRep)
use:
Coalesce(SalesRep, Manager)
// If you want to consider empty and
// blank strings as Null:
Coalesce(EmptyIsNull(Trim(SalesRep)), Manager)
When testing against a list of values, instead of multiple If() or “or”, use the Match() or WildMatch() functions instead.
If (Match(StateCode, 'VA', 'TN', 'FL', 'GA'), 'South', 'Other')
I hope you find these tips useful. You can use my QSDA Pro tool to quickly filter and examine all the uses of the If() function in a Qlik Sense App, located on-prem or in SaaS.
-Rob
