Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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?
I managed to find the solution to this:
Needed to add:
set Concatenate=;
$(Concatenate)
Load *
FROM source
set Concatenate = concatenate;
STORE...
NEXT;
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:
Table1:
LOAD 1 as A, 1 as B, 1 as C
Autogenerate (0);
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:
Concatenate (Table1)
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.
The problem is I need to give each field a label as I want to avoid any automatic joins.
Would there be another way such as using IF Statements?
Hello Rajiv
You can use the wildcard as Gysbert suggest, then you can rename the fields using a mapping table:
FieldMap:
Mapping SQL SELECT oldnames, newnames FROM datadictionary;
Rename fields using FieldMap;
Regards
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.
Hello
May be something like:
LOAD
FileName() As Source,
*
FROM ....
Hi,
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.
I managed to find the solution to this:
Needed to add:
set Concatenate=;
$(Concatenate)
Load *
FROM source
set Concatenate = concatenate;
STORE...
NEXT;