Loading a field that exists only in certain Excel files

[Anonymous]

Hi all,

I would like to load a new field that exists in only some Excel files. The majority of Excel files do not have this column and I would like it to be null if it does not exist.

I tried to do what Gysbert suggested in this discussion but am unsure of how to do so: Load a field if it only exists in the table | Qlik Community

Am I using concatenate in the wrong place? The error output is "Field not found - <NewColumn>".

A simplified version of my load script is as below:

LET QVDExists = not isnull(QVDCreateTime('lib://Path\Table.QVD'));

FOR Each File in filelist ('lib://Path\Excel 201*.xlsx')

Table1:

LOAD

1 as "NewColumn"

Autogenerate(0);

Table:

LOAD

fields,

"fields calculated using fields manipulated" as newFields,

if(isnull("NewColumn"), "N/A", "NewColumn") as NewColumn

Concatenate(Table1)

LOAD

"fields manipulated" as "fields"

FROM '$(File)' (ooxml, embedded labels, table is [Table]);

NEXT;

store Table into 'lib://Path\Table.QVD';

if (QVDExists) then

Concatenate

LOAD

fields

FROM [lib:path\Table.QVD] (qvd) where not exists("oneField");

end if

[Anonymous]

*Updated - managed to load successfully but a synthetic key (with all fields except newFields and %ItemID) and a Table-1 has been created. Any ideas? Thank you!

LET QVDExists = not isnull(QVDCreateTime('lib://Path\Table.QVD'));

FOR Each File in filelist ('lib://Path\Excel 201*.xlsx')

Table:

LOAD

Null() as "NewColumn"

Autogenerate(0);

Concatenate(Table)

LOAD

autonumber("A"&"B"&C&"D") as %ItemID,

fields,

"fields calculated using fields manipulated" as newFields,

LOAD

"fields manipulated" as "fields"

FROM '$(File)' (ooxml, embedded labels, table is [Table]);

NEXT;

store Table into 'lib://Path\Table.QVD';

if (QVDExists) then

Concatenate

LOAD

fields

FROM [lib:path\Table.QVD] (qvd) where not exists("oneField");

end if

[jonathandienst]

How about this?

if (QVDExists) then

Concatenate(Table1)

LOAD

[Anonymous]

Hi Jonathan,

Using this:

if (QVDExists) then

Concatenate(Table1)

LOAD

fields

FROM [lib:path\Table.QVD] (qvd) where not exists("oneField");

end if

one of the "fields" is not able to be found for some reason.. Also Table1 is dropped at the end of the script to prevent synthetic key creation.

Tried using the below codes which work but the "NewColumn" field doesn't appear:

if (QVDExists) then

Concatenate(Table1)

LOAD * ;

Concatenate

LOAD

fields

FROM [lib:path\Table.QVD] (qvd) where not exists("oneField");

end if