Creating a master calendar from an Excel file.

[jjordaan]

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.

[hic · Accepted solution]

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

[jjordaan]

Henric,
Thanks for your help.
I can now proceed with making the calendar