Qlik Community

Ask a Question

Knowledge Base

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY

Wildcard load combined with preceeding load generates synthethic key

ToniKautto
Employee
Employee

Wildcard load combined with preceeding load generates synthethic key

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

User-added image

 

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

no syn key.jpg

 

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;​

 

 

Labels (1)
Version history
Revision #:
3 of 3
Last update:
‎2020-08-24 02:03 AM
Updated by:
 
Contributors