topic Re: Load a field if it only exists in the table in QlikView

Load a field if it only exists in the table

Wed, 06 May 2015 13:54:57 GMT

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?

Re: Load a field if it only exists in the table

Gysbert_Wassenaar — Wed, 06 May 2015 14:03:07 GMT

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.

Re: Load a field if it only exists in the table

Wed, 06 May 2015 14:05:48 GMT

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?

Re: Load a field if it only exists in the table

julian_rodriguez — Wed, 06 May 2015 14:10:03 GMT

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

Re: Load a field if it only exists in the table

Anonymous — Wed, 06 May 2015 14:10:05 GMT

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.

Re: Load a field if it only exists in the table

Wed, 06 May 2015 14:49:42 GMT

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.

Re: Load a field if it only exists in the table

julian_rodriguez — Wed, 06 May 2015 14:52:29 GMT

Hello

May be something like:

LOAD

FileName() As Source,

FROM ....

Re: Load a field if it only exists in the table

Wed, 06 May 2015 15:55:34 GMT

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.

Re: Load a field if it only exists in the table

Wed, 06 May 2015 15:58:41 GMT

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.

Re: Load a field if it only exists in the table

Wed, 06 May 2015 16:53:07 GMT

I managed to find the solution to this:

Needed to add:

set Concatenate=;

$(Concatenate)

Load *

FROM source

set Concatenate = concatenate;

STORE...

NEXT;