Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load a field if it only exists in the table

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?

1 Solution

Accepted Solutions
Not applicable
Author

I managed to find the solution to this:

Needed to add:


set Concatenate=;


$(Concatenate)


Load *

FROM source


set Concatenate = concatenate;

STORE...

NEXT;

View solution in original post

9 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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?

julian_rodriguez
Partner - Specialist
Partner - Specialist

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

Anonymous
Not applicable
Author

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.

Not applicable
Author

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.

julian_rodriguez
Partner - Specialist
Partner - Specialist

Hello

May be something like:

LOAD

     FileName() As Source,

     *

FROM ....

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

I managed to find the solution to this:

Needed to add:


set Concatenate=;


$(Concatenate)


Load *

FROM source


set Concatenate = concatenate;

STORE...

NEXT;