Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
*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
How about this?
if (QVDExists) then
Concatenate(Table1)
LOAD
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