Is it possible when loading data into QlikView that it first checks if a set of fields exist, if it does, then it loads them in together with the other fields in the script. If they do not exist, then it still loads the other fields that are there.
It's because sometimes my data has 3 additional fields if data has been populated in those fields. If those fields are null, then the report does not bring those fields in. I cannot edit the report.
I found when I wrote SET ErrorMode = 0; and that it just skips that particular file. I do not want it to do that.
Would anyone be able to help?
Solved! Go to Solution.
That's possible if you do a wildcard load: LOAD * FROM source. Then it will simply load all the fields that do exist.
If some fields don't always exists you can first do a dummy load:
LOAD 1 as A, 1 as B, 1 as C
This will create an empty table with fields A, B and C.
You can now load data from a source that's missing field C:
LOAD A, B FROM some_source;
By specifying Concatenate you force the loading of the data to Table1. Field C doesn't exist in the source, so null values will be added for field C.
You can use the wildcard as Gysbert suggest, then you can rename the fields using a mapping table:
Mapping SQL SELECT oldnames, newnames FROM datadictionary;
Rename fields using FieldMap;
Take a look in help at the ScriptError. It is set to 11 if a filed is not found. Probably you can use it as a condition in the script to get what you need.
If we do a WildCard Upload, is it possible to bring in the FileName within the *? I am bringing multiple files in via a loop and then storing the data into a QVD.
I tried this in my script and it doesn't seem to be work properly within the loop. It looks like it has created another table for the data which does not have the 3 additional rows.
Is there a way I can use the concatenate and store function together:
I have a For each file loop and it stores each file into a single qvd file. I think it is treating the files without the 3 additional columns as a separate table.