29 maart 2022 Concatenating tables in Qlik Sense Deel dit bericht 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, see you next time! Want to know how you can keep your concatenated loads optimized? Then read our blog post about optimized concatenation. 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 Script Solution Hoe kunnen we je ondersteunen? Barry beschikt over meer dan 20 jaar ervaring als architect, developer, trainer en auteur op het gebied van Data & Analytics. Hij is bereid om je te helpen met al je vragen. Bel ons Mail ons 4 december 2024 New critical security patches for Qlik Sense Enterprise for Windows A new security vulnerability in Qlik Sense Enterprise for Windows has been disclosed, affecting versions from February 2023 to November 2024. Ensure your systems are updated with the latest patches to protect against this issue. New Release Qlik Vulnerability 27 november 2024 Structured Data vs Unstructured Data The difference between structured and unstructured data is fundamental to data management and analytics. Here’s an overview of the two types. Qlik 8 oktober 2024 Artificial Intelligence, Machine Learning, and Deep Learning Explained: How They Impact Your Business In today’s rapidly evolving technological landscape, Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL) are transforming industries and redefining how businesses operate. In this blog post, we will break down these three definitions and elaborate on them. AI
4 december 2024 New critical security patches for Qlik Sense Enterprise for Windows A new security vulnerability in Qlik Sense Enterprise for Windows has been disclosed, affecting versions from February 2023 to November 2024. Ensure your systems are updated with the latest patches to protect against this issue. New Release Qlik Vulnerability
27 november 2024 Structured Data vs Unstructured Data The difference between structured and unstructured data is fundamental to data management and analytics. Here’s an overview of the two types. Qlik
8 oktober 2024 Artificial Intelligence, Machine Learning, and Deep Learning Explained: How They Impact Your Business In today’s rapidly evolving technological landscape, Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL) are transforming industries and redefining how businesses operate. In this blog post, we will break down these three definitions and elaborate on them. AI