About Table Concatenation

Concatenation

 According to the Qlikview Reference Manual:

If two or more statements result in tables with identical field names, the tables are concatenated and treated as one logical table.

Let’s explore this statement. Consider the load script:

Table1:
LOAD * Inline
[
A, B, C
1, 2, 3
4, 5, 6 ];

Table2:
LOAD * Inline
[ A, B, C
6, 5, 4
4, 5, 6 ];

If you run this script, there will only be one table – Table1. This table contains all 4 lines loaded, including the line 4, 5, 6, which will be loaded twice. Click File | Table Viewer (or Ctrl-T) and check the table contents in Table1.

Now if we add the lines:

Table3:
LOAD * Resident Table1;

Table3 will not be created, as it contains the same fields as Table1 and will be concatenated. Now there will be 8 records in Table1. So if the tables have the same fields, they will autoconcatenate. The field order is unimportant, but remember that QV field names are case sensitive.

Avoiding Automatic Concatenation

If the table is created with different fields, then the second table will not be concatenated. But what if you need to reload the data from the resident table? Add the lines

Table4:
NoConcatenate
LOAD * Resident Table1;

Now Table4 is not concatenated to Table1. (If the scripts end at this point we will have a synthetic key, but that is the subject of another post).

Note that if the second table is created with different fields (a subset of the first table’s fields), then it will not be concatenated, even subsequent statements add the missing field(s) or drop the extra fields. Look at this example:

Table1:
LOAD * Inline
[ A, B, C
1, 2, 3
2, 5, 6
3, 6, 7 ];

Table2:
LOAD A, B
Resident Table1;

 Join(Table2)
LOAD A, (A+B) As C
Resident Table1;

Table2 will be created as a new table because it contains only a subset of Table1’s fields when it is created. This does not change when we add a column C to the table as the table already exists.

So the manual text should more accurately say:

If two or more statements create tables with identical field names, the tables are concatenated and treated as one logical table.