20 July 2022 What is the difference between the Alt and Coalesce functions in Qlik Sense? 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 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? More from the Bitmetric team Take your Qlik skills to the next level! Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level. Join the team! Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening. Friday Qlik Test Prep Functions Solution How can we help? Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have. Call us Mail us 25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability 29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution 22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization
25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability
29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution
22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization