Concatenating tables in Qlik Sense

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: