Create a Master calendar using For loop.

[sachin08]

Hi,

I need Master calendar  on SalesDate using For loop

Can anyone tell me what is wrong with my syntax here?  

Sales:

Load * Inline [

SalesDate

02/11/2010

01/11/2011

02/01/2012

02/01/2012

01/04/2012

02/11/2013

] (delimiter is '|');

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; 

DROP TABLE MinMaxDates;

FOR i=1 TO vNumDates

      MasterCalendar:  // Much much slower than the next approach

      LOAD

        SalesDate,

        Year(SalesDate) AS Year,

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

        Month(SalesDate) AS Month,

        Day(SalesDate) AS Day;

    LOAD

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

    AUTOGENERATE 1;

NEXT

[Gysbert_Wassenaar]

You don't need a loop. See this blog post: The Master Calendar. Works just as well for Qlik Sense as for Qlikview.

[petter]

Listen to Gysbert's advice - I avoid FOR loops at all costs as they are up to 600-700 times as slow as using an full AUTOGENERATE ....

However - your problem is that you have to specify explictly the Date format in your INLINE table by using the Date#() number/date interpretation function like this:

Sales:

Load Date#(SalesDate,'MM/DD/YYYY') AS SalesDate Inline [

SalesDate

02/11/2010

01/11/2011

02/01/2012

02/01/2012

01/04/2012

02/11/2013

] (delimiter is '|');