1 Reply Latest reply: Sep 14, 2017 5:33 AM by daniel melo RSS

    Qlik Sense Cloud: Problem loading multiple excel files

    daniel melo

      Hi all,

       

      I am new with Qlik and I'm stuck in such a problem:

      I have several files from different countries. Every country upload a file from a different year (currently from 2016 to 2017). The structure of the file is like Country_Year_Sourcefil.xlsx

      Captura1.png

      This files are divided by different sections: Level_1, Level_2, Level_3 and so on...

       

      Captura2.jpg

       

      What I want to have is a transposed table like this:

      Capture3.png

      For every country and every year, I'll have 1 row. Now what I do is read the name of file, to create the fields Year and Country and then use generic load to "transpose" the data:

       

      for each vFile in FileList('lib://my_path/*.xlsx')

      Nivel1_1:
      LOAD RowNo() as Key1,

                    SubField(FileName(), '_', 1) AS Region,

          SubField(FileName(), '_', 2) AS Year,
          [F3] as "Level1_field,
          [F4] as "Level1_value  

      FROM [$(vFile)]
      (ooxml, embedded labels, header is 1 lines, table is [Level 1_1])
      where not IsNull([F3]);


      Nivel1_2:
      LOAD RowNo() as Key2,
          [C] as "Level2_field",
      [D] as "Level2_value"
      FROM [$(vFile)]]
      (ooxml, no labels, header is 1 lines, table is [Level 1_2])
      where not IsNull([C]);

       

      Nivel1_3:
      LOAD RowNo() as Key3,
          [F3] as "Level3_field",
      [F4] as "Level3_value"
      FROM [$(vFile)]]
      (ooxml, embedded labels, header is 1 lines, table is [Level 1_3])
      where not IsNull([F3]);

      next vFile


      //Transpose Data


      Final1:
      Generic Load *
      Resident Nivel1_1;

       

      Final2:
      Generic Load *
      Resident Nivel1_2;


      Final3:
      Generic Load *
      Resident Nivel1_3;


      Drop table Nivel1_1;

      Drop table Nivel1_2;

      Drop table Nivel1_3;

       

      The problem is that I get a strange structure in the data model and I can't get a structure as the desired transposed excel.

      Any advice about how to get a better approach.

       

      Regards,

       

      Daniel

        • Re: Qlik Sense Cloud: Problem loading multiple excel files
          daniel melo

          I find a way to manage it. If anyone has the same problem here is the code:

           

          Nivel1_1:
          LOAD SubField(FileName(), '_', 1) AS Region,
               SubField(FileName(), '_', 2) AS Year,
              [F3] as Indicator,
          [F4] as Value
          FROM [lib://path_file/name_file.xlsx]
          (ooxml, embedded labels, header is 1 lines, table is [Level 1_1])
          where not IsNull([F3]);

          Nivel1_2:
          LOAD SubField(FileName(), '_', 1) AS Region,
               SubField(FileName(), '_', 2) AS Year,
               [C] as Indicator,
            [D] as Value
          FROM [lib://path_file/name_file.xlsx]
          (ooxml, no labels, header is 1 lines, table is [Level 1_2])
          where not IsNull([C]);

          Nivel1_3:
          LOAD SubField(FileName(), '_', 1) AS Region,
               SubField(FileName(), '_', 2) AS Year,
              [F3] as Indicator,
          [F4] as Value
          FROM [lib://path_file/name_file.xlsx]
          (ooxml, embedded labels, header is 1 lines, table is [Level 1_3])
          where not IsNull([F3]);

          Nivel1_4:
          LOAD SubField(FileName(), '_', 1) AS Region,
               SubField(FileName(), '_', 2) AS Year,
              [F3] as Indicator,
          [F4] as Value
          FROM [lib://path_file/name_file.xlsx]
          (ooxml, embedded labels, header is 1 lines, table is [Level 1_4])
          where not IsNull([F3]);

          Table2:
          generic load *
          resident Nivel1_1;

          ResultTable:
          LOAD Distinct Year, Region Resident Nivel1_1;

          FOR i = 0 to NoOfTables()
            TableList:
            LOAD TableName($(i)) as Tablename AUTOGENERATE 1
            WHERE WildMatch(TableName($(i)), 'Table2.*');
          NEXT i

          FOR i = 1 to FieldValueCount('Tablename')
            LET vTable = FieldValue('Tablename', $(i));
            LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];
            DROP TABLE [$(vTable)];
          NEXT i

          Drop Tables Nivel1_1, TableList;