Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
HAAM
Contributor II
Contributor II

Generate month and year fields for a data set that don't contain date fields

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   

 

Labels (2)
11 Replies
HAAM
Contributor II
Contributor II
Author

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);

marcus_sommer
MVP & Luminary
MVP & Luminary

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