Wildcard load combined with preceeding load generates synthethic key

[ToniKautto]

Wildcard load combined with preceding load generates a synthetic key, as explained in the example below. The reason for this is in line with how loads are executed in QlikView, and therefore this is considered to be according to design.

The scenario prerequisites;

• Load from multiple files by wildcard, for example, Data*.txt
• The wildcard load is preceded by a load that adds a new field to the final table

T1:
LOAD
F1,
F2,
F1 & F2 as F3;
LOAD
F1,
F2
FROM 'Data*.txt' (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

 In the example below the data is loaded from 3 files;

• Data1.txt
• Data2.txt
• Data3.txt

The wildcard load creates the following iteration path;

1. QlikView identifies the first Data*.txt file as Data1.txt
2. Data1.txt is loaded into memory as table Data1, containing the fields F1 and F2
3. Data1 is forwarded as input to the preceding load.
4. T1 table does not exist so Data1 vanishes as it becomes T1, consisting of the fields F1, F2 and F3
5. QlikView identifies the next Data*.txt file as Data2.txt
6. Data2.txt is loaded in memory as table Data2, containing the fields F1 and F2
7. Data2 is forwarded as input to the preceding load.
8. QlikView attempts to concatenate Data2 with T1, but fails since they do not have the same fields.
9. Data2 is left as a table and synthetic key F1 + F2 is created.
10. QlikView identifies the next Data*.txt file as Data3.txt
11. Data3.txt is loaded auto-concatenated into Data2, since they have the same fields F1 and F2
12. Data2 is again forwarded as input to the preceeding load.
13. QlikView attempts to concatenate Data2 with T1, but fails since they do not have the same fields.
14. Data2 is left as a table and synthetic table is updated with the new values from Data3.txt
15. The result of the load is a below with two tables and a synthetic table

The expected result is to get only one table with all four fields.

Resolution:

This is resolved by making the wildcard load into a temporary table and then reloading it as a resident table.

1. Make the wildcard load into a temporary table

T1_TMP:
LOAD
F1, 
F2, 
F3
FROM
'*.txt'
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);​

• Load the temporary table as resident in to a new table

T1:
LOAD
*, 
F1 + F2 as F4
Resident T1_TMP;​

• Drop the temporary table

DROP Table T1_TMP;​