Create a Master calendar using For loop.

sachin08 — Thu, 09 Aug 2018 10:04:02 GMT

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/21/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:

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;

Re: Create a Master calendar using For loop.

sunny_talwar — Thu, 09 Aug 2018 16:47:36 GMT

Besides this being really slow... I had no problems running your script

Sales:

LOAD * INLINE [

SalesDate

02/11/2010

01/11/2011

02/01/2012

02/21/2012

01/04/2012

02/11/2013

];

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:

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

My environmental variables were like this

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

topic Create a Master calendar using For loop. in QlikView

Create a Master calendar using For loop.

Re: Create a Master calendar using For loop.