3 Replies Latest reply: Nov 22, 2017 6:24 AM by Mei Zheng Tay RSS

    Loading a field that exists only in certain Excel files

    Mei Zheng Tay

      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

        • Re: Loading a field that exists only in certain Excel files
          Mei Zheng Tay

          *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