Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can anyone help me with this requirement.
I have more than 30+ Excel tabs with Name of the Month-Year & their respective Data in the tabs.
I have to automatically loa
d the excel tabs & create a name of the tab as Month -Year
Tab Name - Jan -2021
Customer | Value |
A | 123 |
B | 345 |
C | 567 |
Tab Name - Feb - 2021
Customer | Value |
D | 1 |
E | 2 |
F | 3 |
Tab Name - Mar - 2021
Customer | Value |
G | 987 |
H | 765 |
I | 67 |
here is the required Output, Can anyone please help me with this
Output:
Customer | Value | Month Year |
A | 123 | Jan-21 |
B | 345 | Jan-21 |
C | 567 | Jan-21 |
D | 1 | Feb-21 |
E | 2 | Feb-21 |
F | 3 | Feb-21 |
G | 987 | Mar-21 |
H | 765 | Mar-21 |
I | 67 | Mar-21 |
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;