Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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,
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;
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,
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;