How does Qlik Sense load the data of the previous 12 months of the current month in excel sheet data?

[Gin009]

Hi guys，

     I have an excel with 12 sheets of data. How should I set a date range parameter and then load the first 12 months of the current month's data through a SQL on qlik sense?

    Please help, thanks！！！

 

[steeefan]

For the previous 12mon excluding the current one, you could use a loop to create each date and then load from your Excel file. Try this:

Data:
NOCONCATENATE LOAD * INLINE [
  Date
];

FOR i = 0 TO 12

  LET vMonthEnd = Date(MonthEnd(AddMonths(Today(), ($(i)+1) * -1)), 'YYYYMMDD');
  TRACE $(vMonthEnd);

  CONCATENATE (Data) LOAD
    *
  FROM
    [lib://Storage/File.xlsx]
    (ooxml, embedded labels, table is [$(vMonthEnd)]);

NEXT i;

In the INLINE LOAD, replace Date by a field you have in your Excel file.

[Gin009]

hi @steeefan 

    I tried following your method, but it ended up not loading the year in a loop

[steeefan]

If seems that after you set vTargetReportingDate once, you are just keeping that same value in every interation.

If you want do show code, do not post a screenshot of it but just paste the text. That will make it much easier referencing to it or copying a line from it.

[Gin009]

hi @steeefan 

    Thanks for your support!!!

    I tried to test according to the above script, but it will keep looping and reporting errors. How can I correctly load the dates of the sheets page in excel into the data?

Data:
NOCONCATENATE
load null() as dummy AutoGenerate(1);

FOR i = 0 TO 12

LET vMonthEnd = Date(MonthEnd(AddMonths(Today(), ($(i)+1) * -1)), 'YYYYMMDD');

TRACE $(vMonthEnd);

CONCATENATE (Data)
LOAD
*
FROM
[lib://AttachedFiles/test.xlsx]
(ooxml, embedded labels, table is [$(vMonthEnd)]);

NEXT i;

[steeefan]

Are you sure that there is a tab by the name of "20230331" in your Excel file and that it contains data matching the load definition?

[Gin009]

hi @steeefan 

How can I skip this error when I don't have data for these months? So that the script can load normally, but the data without dates will not be displayed.

[steeefan]

You can use a different ErrorMode:

SET ErrorMode = 0;