20 July 2022

What is the difference between Alt and Coalesce?

Share this message

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:

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 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.

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’).

Are these questions too easy for you?

Masters Summit for Qlik 2022 - New Orleans and Madrid

If these questions are too easy for you, then you may be ready for the Masters Summit for Qlik.

At the Masters Summit for Qlik, we take your Qlik skills to the next level in 3 days with deep dives about:

  • Qlik Data Modelling
  • Qlik Scripting
  • Set Analysis and Advanced Aggregations
  • Solution Architecture & Performance
  • Effective Visualizations
  • Performance Tuning
  • Qlik Sense integration

The courses are taught by Qlik veterans Rob Wunderlich, Oleg Troyanksy, Bill Lay, Nick Webster and Bitmetric’s Barry Harmsen. Together, this team brings almost 75 years of Qlik experience and has implemented hundreds of successful projects.

You can find more information and registration on the Masters Summit for Qlik website.

See you next week!

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

How can we help?

Feel free to contact us if you have any comments or questions.