What is the difference between the Alt and Coalesce functions in Qlik Sense?

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about the Alt() and Coalesce() functions in Qlik Sense, and how they differ from each other:

The correct answer is B: Coalesce(SizeName, ’Unknown’) AS SizeName

The disparity between the answers this week shows that this function is not well known yet, but Coalesce() has made it to Qlik in recent years. And being the correct answer in this question, why is the answer Coalesce() and not any of the others?

The difference between Alt and Coalesce in Qlik

The main difference between Alt() and Coalesce() is that Coalesce() returns the first non-null value of the given statement, while Alt() returns the first valid number representation. In this case we are looking to fill a text value (small, medium and large) and not a number, so Alt() will not work.

Before Coalesce() was added to Qlik, the go to solution would be to write an If-statement to check for empty values, the solution being something almost equal to answer C. However in this example the else statement of the If() expression is omitted, by which we only get ‘unknown’ as result and not the original SizeName. The proper expression should be: If(IsNull(SizeName), ‘Unknown’, SizeName) AS SizeName.

Concluding, the Coalesce() function is the more elegant solution in this case. Using just Coalesce(SizeName, ‘Unknown’) AS SizeName. The syntax for Coalesce is as following:

Coalesce(Expr1, [Expr2, Expr3, ....], else)

For Coalesce() it is possible to give unlimited arguments to compare. When all of these arguments return null the final position in the syntax can be used to give a return value. In the question ‘Unknown’ was used, so we can quickly select this in the front end to check the overall data quality.

Other things to consider when using Coalesce()

One thing to note is that Coalesce() makes for a great combination with EmptyIsNull(). Since Coalesce() returns the first non null value, we need to make sure that all values are true null. Using EmptyIsNull() transforms all empty values into a null value. So instead of long strings like, IF(LEN((SizeName) = 0, Null(), SizeName) you could use Coalesce(EmptyIsNull(SizeName), ‘Unknown’).

That’s it for this week. See you next Friday?

Want more? Then click the button below for our full archive!