Qlik Sense Cloud: Problem loading multiple excel files

[danimelo1]

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

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

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

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,
    as "Level2_field",
as "Level2_value"
FROM [$(vFile)]]
(ooxml, no labels, header is 1 lines, table is [Level 1_2])
where not IsNull();

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

[danimelo1 · Accepted solution]

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,
     as Indicator,
  as Value
FROM [lib://path_file/name_file.xlsx]
(ooxml, no labels, header is 1 lines, table is [Level 1_2])
where not IsNull();

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;