Hi, there are a lot similar topics in forum how to loop load through all excel sheets. For example:
https://community.qlik.com/t5/New-to-Qlik-Sense/Load-multiple-sheets-from-Excel-Files/td-p/1595476
Trickiest part for you is to have list of sheet names, as Qlik is hardly can fetch it from excel. But if your sheet names are consistent and have 'MMM - YYYY' format it possible to generate that, and loop through all of them with something like this:
Set ErrorMode = 0; //ignore errors if there will be no sheets anymore
LET vCalendarStart = Date('2021-01-01'); // Static start,
LET vCalendarEnd = Date('2023-12-31'); //or you can make it dynamic with yearend(addyears(today(),-2))
LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;
Calendar_temp:
LOAD DISTINCT
date(floor(MonthEnd(Date)), 'MMM - YYYY') as sheet_date; //your sheet name format
LOAD
Date($(#vCalendarStart) + RecNo()-1) AS Date
AutoGenerate $(#vCalendarLength);
Calendar:
LOAD
RowNo() as row, //adding number for easier loop
sheet_date
RESIDENT Calendar_temp;
DROP TABLE Calendar_temp;
LET vTotal_rows = NoOfRows('Calendar');
//LOOP THROUGH SHEETS
FOR sheet = 1 to $(vTotal_rows)
//TAKING SHEET WHICH WE WILL LOOK FOR
sh:
LOAD
sheet_date
RESIDENT Calendar
WHERE row = $(sheet);
LET vSheetName = peek('sheet_date','0','sh');
DROP TABLE sh;
//LOAD DATA FROM THAT SHEET
main:
Load
*,
'$(vSheetName)' as MonthYear
FROM [your excel.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [$(vSheetName)]);
NEXT sheet;
