10 August 2022 Number formatting and preceding zeroes 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 number formatting and preceding zeroes in Qlik Sense: The correct answer is A. An interesting question with an unexpected result. As many thought it would be the logical answer D, it is in fact something completely different. Truth be told we were surprised as well in the beginning, as it is more of a Qlik quirk than lack of knowledge. So what does happen here and why? The explanation is as simple as maybe frustrating if you don’t know what to expect. When loading the data Qlik uses the first format it encounters for the remaining load. However, this only is applicable to the exact number being loaded. Let’s use the question to explain. For a numeric value in a field, Qlik uses the first format that it encounters In the first inline table of the question 0040 is loaded as the first value. Subsequently it loads 40 and 41. However, since 40 matches the previously loaded numeric value of 0040 it is converted to the same format, being 0040. Next on the load sequence is row number three containing 41. However this is not a match to the numeric value of 40 (and thus the format of 0040), so Qlik starts again with the interpretation of the format, loading it normally as 41. This exact same logic works for the second inline table, which is being joined to the first. In this table we now can make up that 40 is loaded before 0040, hence showing both as 40. To bring this into a bit more perspective, let’s have a look at the inline table below and the subsequently loaded data model: Table1:LOAD * INLINE [Dim, Amount1a, 0040b, 40c, 41d, 0041e, 040f, 042g, 42h, 00040]; Here we can clearly see that for each new loaded individual number Qlik interprets the loaded format and uses this in the subsequent load. The dim containing the 40 values at dimensions b, e and h are all formatted as the first loaded 40 at dim a. The same goes for rows c and d in which 41 is formatted as it’s first load, and at last f and g where 42 is loaded as 042. So, depending on how you receive the data this can impose some problems especially in large quantities of data with limited data quality. If you encounter this and want to solve it, it is simply fixed by using the Num() function. Num(Amount1) as Amount1 would solve it in this case and formats all numeric values equally. And in the case you do need the preceding zeroes, this is fixed by using the Text() function, leaving the loaded values just as you received them. 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 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 11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik
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
11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik