Master Calendar Using For Loop

sunitha_chellaiah — Tue, 07 Aug 2018 09:52:06 GMT

Hi All,

I need simple example of Master Calendar using For Loop.

Can anybody suggest?

Thanks.

Re: Master Calendar Using For Loop

petter — Tue, 07 Aug 2018 10:20:20 GMT

You would normally have a fact table/transaction-like table with the range of dates in question but possibly with some holes regarding some or many dates. You would normally want your master calendar to start at the first date and end at the last date or a few months into the future - for forecasting or estimates for example.

MinMaxDates:

LOAD Min(SalesDate) AS minDate,Max(SalesDate) AS maxDate RESIDENT Sales;

vMinDate=Num(Peek('minDate',0,'MinMaxDates'));

vMaxDate=Num(Peek('maxDate',0,'MinMaxDates'));

vNumDates = vMaxDate-vMinDate+1; // inclusive the min and max

DROP TABLE MinMaxDates;

// Here comes two different approaches - #1 is based on a FOR loop and #2 on AUTOGENERATE n

FOR i=1 TO vNumDates

MasterCalendar1: // Much much slower than the next approach

LOAD

Date,

Year(Date) AS Year,

Ceil(Month(Date)/3) AS Quarter,

Month(Date) AS Month,

Day(Date) AS Day;

LOAD

Date( $(vMinDate) + $(i) - 1 ) AS Date

AUTOGENERATE 1;

MasterCalendar2: // With LOAD iteration based on AUTOGENERATE and RowNo() - very quick.

LOAD

Date AS Date2,

Year(Date) AS Year2,

Ceil(Month(Date)/3) AS Quarter2,

Month(Date) AS Month2,

Day(Date) AS Day2;

LOAD

Date( $(vMinDate) + RowNo() - 1 ) AS Date

AUTOGENERATE $(vNumDates);

topic Master Calendar Using For Loop in QlikView

Master Calendar Using For Loop

Re: Master Calendar Using For Loop