29 March 2022

Concatenating tables in Qlik Sense

Share this message
Learn how to concatenate tables in Qlik Sense or QlikView, and what to look out for.

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:

When we execute the load script on the tables in figure 1. What is the expected outcome in the model viewer?

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:

Example of how to use the NoConcatenate prefix in Qlik Sense

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:

Two example tables in Qlik that do not share the exact same columns. Will be used to demonstrate the CONCATENATE prefix.

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.

When two tables share the exact same fields, they will be automatically concatenated into a single table by Qlik Sense (or QlikView)

The resulting table will be:

The concatenated table

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:

Example of a Qlik script using the Concatenate prefix.

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:

Concatenated table in Qlik

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:

The two tables that we'll be concatenating in Qlik Sense

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:

The combined script for concatenating tables in Qlik Sense or QlikView

This results in the following table:

The combined, concatenated table in Qlik.

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!

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

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.