how to identify if a field is present in the load statement?

[Anonymous]

I have am using loop to load 5 similar files. There is one field called [Critical Care Customers] that is present in only one file and is missing in the rest of the files. If this field is missing in any of the files i need to create this field  in the script such as null () as [Critical Care Customers] in rest of the files while loading.

Can anybody please suggest how to go about this?

[Anil_Babu_Samineni]

Can you use for that variable like

Trace $(Variable);

[Anonymous]

look for ScriptError =11

https://help.qlik.com/de-DE/qlikview/12.1/Subsystems/Client/Content/Scripting/ErrorVariables/ScriptE...

[Peter_Cammaert]

If all files have a similar file name, you can just perform a CONCATENATE LOAD * FROM, and QlikView will do all merging by itself.

Imagine we're reading a set of 5 Excel files with a name consisting of a prefix and a YearMonth pattern::

MergedTable

LOAD * FROM 'Invoices2017??.xlsx' (options);

The safer method would be to use a CONCATENATE LOAD * in your FOR loop, as this rechnique will provide more safeguards for merging tables with a different layout.

[Anonymous]

I am already concatenating the files. The file have a lot of fields and we are not loading all the fields , hence we have stated explicity in the load statement the fields that we want to load. If I add this field then it shows field not found when the QV starts loading the new files where this field does not exist.

so i wanted to add in the script if this field is not present then just load null for this field.

[Anonymous]

did you try SetErrorMode = 0 ?

if ScriptError =11 then you could add a  separate LOAD without the missing field...

[Anonymous]

can you please elaborate more on this?

[Anonymous · Accepted solution]

first

SET ErrorMode = 0;

--> Set it back to default later if needed

then

"LOAD your table with "all" fields..." --> can be done multiple times...

if ($(ScriptError) = 11) then

do an alternative LOAD Statement... for example without the missing field

[swuehl]

You probably know in which file you expect the field?

I would suggest using a loop to load your files, and then check the file name just before the LOAD statement and create a variable conditionally like

FOR EACH vFile IN ...

IF vFile LIKE '*Test* THEN

      LET vField = '[Critical Care Customers]';

ELSE

     LET vField = 'Null() as [Critical Care Customers]';

ENDIF

LOAD FieldA,

          FieldB,

          $(vField)

FROM vFile;

NEXT  vFile

[Anonymous]

Set ErrorMode = 0;

TEMP:
LOAD * INLINE [
F1, F2
A, 1
B, 2
C, 3
];

NoConcatenate

LOAD F1,F2,F3 Resident TEMP;

if $(ScriptError)=11 then

LOAD F1,F2 Resident TEMP;

ENDIF;

Set ErrorMode = 1;