
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Create a Master calendar using For loop.
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You don't need a loop. See this blog post: The Master Calendar. Works just as well for Qlik Sense as for Qlikview.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 '|');
