Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have create an app using qlik sense. The source data is excel files and every file represent one month. So I get every month an excel file to load it. All the files have the same structure, so I use the following approach:
Table:
Load
A,
B,
C,
D,
FROM [lib://AttachedFiles/Jan2019.xlsx]
(ooxml, embedded labels, table is Sheet1);
Concatenate Load
A,
B,
C,
D,
FROM [lib://AttachedFiles/Feb2019.xlsx]
(ooxml, embedded labels, table is Sheet1);
.....
How can I dedicate the month and the year while there is no field can be used as date.
I think it can be solve by generating a number field in every file and use it as month:
Table:
Load
Month,A,B,C,D
1,name,surname,country,city
1,name,surname,country,city
1,name,surname,country,city
FROM [lib://AttachedFiles/Jan2019.xlsx]
(ooxml, embedded labels, table is Sheet1);
Concatenate Load
Month,A,B,C,D
2,name,surname,country,city
2,name,surname,country,city
2,name,surname,country,city
FROM [lib://AttachedFiles/Feb2019.xlsx]
(ooxml, embedded labels, table is Sheet1);
.....
but i don't know how to achieve this in the script while there is too much rows. And how it could be generated automatically every month.
Thanks in advance
Thank you all for the proper solution,
Like you said Marcus the best way is to solve it in beforehand.
But before this card is being closed with the solution.
Can you please explain me the approach to load the date from table name and which functions can be used for that?
FROM [lib://AttachedFiles/mar+apr.xlsx]
(ooxml, embedded labels, table is DE_201903);
There is no native functionality in Qlik else it needs to be done per SQL or VBA/VBS. Here a few examples:
How-to-load-multiple-sheets-on-excel-file-into-Qlikview
Load-data-from-multiple-sheets-of-excel-file-dynamic-sheet-count
Another approach but not easier would be to unzip the XLSX (also per EXECUTE statement) and then reading the various xml-files to find the included sheets.
- Marcus