9 March 2023 Column references 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 column references in Qlik Sense. We were actually quite surprised when we encountered this answer and given the fact that the answers are almost spread equally between A and D it seems you are too! The correct answer is A Column references in Qlik Sense The column() function in Qlik references a column in a straight table. Within the parentheses you can specify which column to target, Column(1) being the first column after all dimensions, since these are disregarded. As we can see in the chart above referencing a column does not imply referencing the actual values in the column, but rather the expression used in it. In the reference column the expression is calculated again. That’s why the column gives different results when using the random function. Calculation speed The use of the Column() syntax is mostly one based on quick debugging, doing on the fly validation or summing multiple columns. And in the latter case, does it speed up things? We have tested this on a dataset containing 5 million rows and a variable for margin: SET vMargin = 1 - (Sum(Revenue) / Sum(CostPrice)); With the help of the Qlik Sense Document Analyzer Pro we can see the effect of the calculations in the duration of the load of the following tables: We have ran the analyzer three times. One time with only the column containing the expression $(vMargin), The other time with $(vMargin) and Column(1). The final time with $(vMargin) in both colomns. QSDA Pro is a great tool to analyze sheets and find out metric about the performance and where to improve. In this case the SaaS app [TST] Column (containing the tables pictured above) is loaded and evaluated: Now if we press view, we can get an overview of all statistics of the app. We press the button ‘Sheets’ for the details on a sheet level: It is quite clear that the loading times are quite long. It takes almost eight seconds to load the table with only the variable. But it is more interesting to see what happens with the other two. Using the Column() function to reference the first column is actually the slowest. It takes three to four seconds longer to load the extra column. So be aware you want to calculate totals on a chart with many columns and measures. Using column will calculate all these measures again per column, accumulating to a heavy and slow loading chart. 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. Expressions Friday Qlik Test Prep Performance Qlik 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