Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
For a customer I have to make a master calendar from an Excel file.
The reason is that the customer has its own financial years and created this in an Excel for the years 2003 to 2021
I have an example of a year are added.
Pay Particular attention to row 2 and row 53.
How should I handle this?
Thank you for your help.
Use intervalmatch. Try the following:
MasterCalendar:
LOAD FY,
WEEK,
DAYS,
BEGIN_DATE,
END_DATE
FROM [Financial Year.xlsx] (ooxml, embedded labels, table is Sheet1);
MinMaxDate:
Load Min(BEGIN_DATE) as MinDate, Max(END_DATE) as MaxDate resident MasterCalendar;
Let vMinDate = peek('MinDate',-1,'MinMaxDate')-1;
Let vMaxDate = peek('MaxDate',-1,'MinMaxDate');
Dates:
Load Date(recno() + $(vMinDate)) as Date AutoGenerate $(vMaxDate) - $(vMinDate);
Left Join (MasterCalendar)
IntervalMatch (Date)
LOAD BEGIN_DATE,
END_DATE
resident MasterCalendar;
Drop Table MinMaxDate, Dates;
/HIC
Use intervalmatch. Try the following:
MasterCalendar:
LOAD FY,
WEEK,
DAYS,
BEGIN_DATE,
END_DATE
FROM [Financial Year.xlsx] (ooxml, embedded labels, table is Sheet1);
MinMaxDate:
Load Min(BEGIN_DATE) as MinDate, Max(END_DATE) as MaxDate resident MasterCalendar;
Let vMinDate = peek('MinDate',-1,'MinMaxDate')-1;
Let vMaxDate = peek('MaxDate',-1,'MinMaxDate');
Dates:
Load Date(recno() + $(vMinDate)) as Date AutoGenerate $(vMaxDate) - $(vMinDate);
Left Join (MasterCalendar)
IntervalMatch (Date)
LOAD BEGIN_DATE,
END_DATE
resident MasterCalendar;
Drop Table MinMaxDate, Dates;
/HIC
Henric,
Thanks for your help.
I can now proceed with making the calendar