Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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?

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

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
MVP & Luminary
MVP & Luminary

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

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

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

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

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

mov
Esteemed Contributor III

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

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

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

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

Hello

May be something like:

LOAD

     FileName() As Source,

     *

FROM ....

Not applicable

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

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

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

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

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

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