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! Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings. 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 10 July 2024 Qlik improvements to sheet navigation Explore Qlik Sense Cloud’s latest update (July 9, 2024) with enhanced sheet navigation and grouping features. Easily manage and find sheets, bookmarks, and notes with the new left-pane navigation. New Release Qlik 28 May 2024 What’s New in Qlik Sense May 2024 for Administrators This Qlik Sense May 2024 release provides Qlik Sense administrators a summary of the features and improvements available. New Release Qlik 28 May 2024 What’s New in Qlik Sense May 2024 for Business Users, Analytic Creators and Data Integrators The Qlik Sense May 2024 release provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available. New Release Qlik
10 July 2024 Qlik improvements to sheet navigation Explore Qlik Sense Cloud’s latest update (July 9, 2024) with enhanced sheet navigation and grouping features. Easily manage and find sheets, bookmarks, and notes with the new left-pane navigation. New Release Qlik
28 May 2024 What’s New in Qlik Sense May 2024 for Administrators This Qlik Sense May 2024 release provides Qlik Sense administrators a summary of the features and improvements available. New Release Qlik
28 May 2024 What’s New in Qlik Sense May 2024 for Business Users, Analytic Creators and Data Integrators The Qlik Sense May 2024 release provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available. New Release Qlik