Hello Qlik Community!
I am trying to do incremental load. The goal was to load all excel files in folder on a monthly basis (loaded by name and wildcard), check unique reference if it exists (let's call it CVD reference which is combination of string and number) and load which CVDs are not present in old table. I cannot use different field - this is the ID I need.
I've managed to make it work, but I am receiving Synthetic key, although all fields in tables are same - I've made sure of that and confirmed that by exporting as CSV files. "oldTable" as an Initial load is already saved as QVD file.
Here is the code I am using:
temp_file:
LOAD
*
FROM [lib://NAS_LOCATION/*filename_monthly.xlsx]
(ooxml, embedded labels, table is [SheetName]);
store temp_file into [lib://NAS_LOCATION/temp.qvd](qvd);
Drop table temp_file;
oldTable: //old QVD file
LOAD *
FROM [lib://NAS_LOCATION/oldTable.qvd]
(qvd);
NewTable: //new QVD file
NoConcatenate
LOAD
*
FROM [lib://NAS_LOCATION//temp.qvd](qvd)
WHERE not Exists("CVD reference");
STORE NewTable into [lib://NAS_LOCATION/oldTable.qvd](qvd);
Few questions are popping up:
- With this script I will always load whole folder of the files. Currently it's fine (3 months data), but in future it might have some impact on performance, which I cannot guess corrrectly. Files are usually between 10-20 MB in XLSX format.
- Is there a way to peek only for the CVD reference?
- Do I have to load all of the files all the time?
- What performance hit could it have in the future? (I know this is a wild guess since you don't know the infrastructure)
- For some reason I receive one synthetic key created as a combination from all of the table fields. I am really not sure why
- What could be the issue?
- How can I find the "max" value of CVD reference (this was approach in one of the tutorials) ?
- What I probably do not understand about incremental load? Was the usage of "where not exists" correct?
Unfortunately I cannot share the files directly, since they contain sensitive financial informations. But in case of need, I can simulate some of the data for you.
Thank you in advance!
