Bitmetric Friday Qlik Test Prep – Week 12 – Automatic Concatenation

By Bitmetric Admin

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:

No alt text provided for this image

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:

[Clients]:
LOAD
  Product,
  Client,
  Amount
FROM [Clients.qvd] (qvd)
;

[TMP_TopClients]:
NoConcatenate
LOAD
  Product,
  Client,
  Amount
FROM [TopClients.qvd] (qvd)
;

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:

No alt text provided for this image

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.

[Table A]:
LOAD
  Product,
  Client,
  'Unknown' as [Sales rep],
  Amount
FROM [Table A.qvd] (qvd)
;

LOAD
  Product,
  Client,
  [Sales rep],
  Amount
FROM [Table B.qvd] (qvd)
;

The resulting table will be:

No alt text provided for this image

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:

[Table A]:
LOAD
  Product,
  Client,
  Amount
FROM [Table A.qvd] (qvd)
;

Concatenate ([Table A])
LOAD
  Product,
  Client,
  [Sales rep],
  Amount
FROM [Table B.qvd] (qvd)
;

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:

No alt text provided for this image

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:

No alt text provided for this image

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:

[Table A]:
LOAD
  Product,
  Client,
  'Picard' as [Sales rep],
  Amount
FROM [Table A.qvd] (qvd)
;

Concatenate ([Table A])
LOAD
  Product,
  Client,
  [Sales rep],
  Amount
FROM [Table B.qvd] (qvd)
;

This results in the following table:

No alt text provided for this image

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:

  • If you have suggestions for questions, we love to hear from you via WhatsApp or at [email protected]
  • If you’re enjoying these questions and want to work on stuff like this every day (but a bit more challenging), we’re always on the lookout for new colleagues. Check our job openings here.

Previous posts

Week 11: Sum(TOTAL)

Week 10: Unpivoting data

Week 9: Statements & Breakpoints

Week 8: Sales & Budget model

Week 7: MonthEnd(Today())

Week 6: Looping Tables

Week 5: Set Identifiers

Week 4: Time series visualization

Week 3: Circular References & Synthetic Keys

Week 2: Section Access

Week 1: Optimized Load