29 March 2022 Concatenating tables 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 automatic table concatenation: This is typically one of the first ‘gotchas’ that new Qlik developers encounter. We’ve all been there, so fortunately everyone got the correct answer: The correct answer is B: Table A with fields Product, Client and Amount The reason for this is automatic concatenation; the data from Table B has automatically been appended to Table A. When does automatic concatenation happen? Automatic concatenation happens whenever two or more tables with identical field names are loaded. When this happens, Qlik will automatically append (concatenate) the rows of the second (and any subsequent identical tables) to the first table that was loaded. The order of the fields within the tables does not matter. That’s why Table B gets automatically concatenated to Table A, even though the field order is different. Can we prevent automatic concatenation? Sometimes we want to prevent Qlik from automatically concatenating two tables, even though they are identical. For example, we may want to perform some calculations on a subset of data and need to load that subset into a temporary table to achieve this. We may simply add an extra, non-matching field to prevent automatic concatenation. While this works fine, a cleaner solution is to use the NoConcatenate prefix: In this example, the result will be two tables: Clients and TMP_TopClients. It’s important to note that if we keep both tables in the data model it will result in the two tables getting joined by a synthetic key. We need to do whatever it is we need to do with the temporary table, integrate the result into the main model and drop the temporary table before the end of the script. Can we concatenate tables that don’t share the exact same columns? Sometimes we want to concatenate two tables that don’t share the exact same columns. For example, consider the following two tables: The first way we can concatenate these two tables together is by ensuring that both tables have the same fields. As we saw before, this way Qlik will use automatic concatenation. The resulting table will be: While this works, it does make the code less readable. Skimming the code you might not notice that both tables are getting concatenated. The Concatenate prefix With the Concatenate prefix, we can force Qlik to concatenate two tables together, even if those tables don’t share the exact same columns. All non-matching fields between the tables will be assigned null values. Modifying our previous script: We’ve removed the Sales rep column from Table A and have added the Concatenate prefix before the load statement of Table B. The result is: As you can see, the Sales rep column gets automatically added to Table A, but it doesn’t contain any values. Specifying the target table You may have noticed that we added Table A between parentheses immediately after the Concatenate prefix: Concatenate ([Table A]) This specifies the target table to which the table needs to be concatenated. If no name is provided, Qlik assumes you mean to concatenate to the last table that was loaded. In this example the result would be the same, as Table A is immediately loaded before. However, we believe that it’s good form to always specify the target table. Specifying the target table makes your intentions explicit and the script more readable, easing future maintenance. (you can read more about this topic in our free Qlik Sense Coding Conventions) Combining both approaches While we think that tables concatenations should always be explicitly stated in the script, there is also a benefit to aligning the columns between multiple tables: you get to choose which default value to use, instead of simple null values. Considering the following two tables again: Perhaps we know that all data in Table A belongs to a Sales rep named ‘Picard’? In that case, rather than forcing null values with the Concatenate prefix, we could also specify the actual values: This results in the following table: Note that we still used the Concatenate prefix on the second table, even though this isn’t necessary as Qlik will use automatic concatenation. As mentioned above, we think this is good form and will make script maintenance easier as the developer’s intentions are clearly stated. That’s it for this week. See you next Friday? And remember: See you at QlikWorld 2023 in Vegas? Come see us at QlikWorld 2023 for a chance to win a free, lifetime SenseTheme subscription and get some cool swag. Want more Friday Qlik Test Prep? Check out the Friday Qlik Test Prep archive for more Qlik questions and answers. Friday Qlik Test Prep Script Solution How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik 23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik 11 May 2023 How to make seasonal trendlines in Qlik Sense 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 how to make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution
23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik
23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik
11 May 2023 How to make seasonal trendlines in Qlik Sense 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 how to make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution