topic Creating a master calendar from an Excel file. in QlikView

Creating a master calendar from an Excel file.

jjordaan — Mon, 19 Mar 2012 13:28:38 GMT

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.

Creating a master calendar from an Excel file.

hic — Mon, 19 Mar 2012 18:17:19 GMT

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

Creating a master calendar from an Excel file.

jjordaan — Mon, 19 Mar 2012 19:44:11 GMT

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