Master Calendar error

[Zaga_69]

Hi all 🙂

I am struggling with the master calendar...

I have implemented the following code: 

MasterCalendar:
Load
TempDate AS DateKey,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
//Date#((Date(monthstart(TempDate), 'YYYYMM')),'YYYYMM') As YearMonth,
Year(TempDate)&date(TempDate,'MM') As YearMonth,
Date(MonthStart(TempDate), 'YYYYMM') as YearMonth2,
// Date(monthstart(TempDate), 'YYYYMMDD') As YearMonth,

WeekDay(TempDate) as WeekDay;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('DateKey', recno()))-1 as mindate,
//'20170101' as mindate,
max(FieldValue('DateKey', recno())) as maxdate
// '20180101' as maxdate
AUTOGENERATE FieldValueCount('DateKey');

but I got the following error message:

The following error occurred:

Autogenerate: generate count is out of range

The error occurred here:

LOAD min(FieldValue('DateKey', recno()))-1 as mindate, max(FieldValue('DateKey', recno())) as maxdate AUTOGENERATE FieldValueCount('DateKey')

I was looking for a solution but I was not succesful.

Any idea will be much appreciated.

Best regards,

Edi

[sunny_talwar · Accepted solution]

Not sure, but this could be your DateKey field format... I did this... and it worked for me

LOAD * INLINE [
    DateKey
    20170101
    20180101
];

MasterCalendar:
LOAD TempDate AS DateKey,
	 week(TempDate) As Week,
	 Year(TempDate) As Year,
	 Month(TempDate) As Month,
	 Day(TempDate) As Day,
	 'Q' & ceil(month(TempDate) / 3) AS Quarter,
	 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
	 //Date#((Date(monthstart(TempDate), 'YYYYMM')),'YYYYMM') As YearMonth,
	 Year(TempDate)&date(TempDate,'MM') As YearMonth,
	 Date(MonthStart(TempDate), 'YYYYMM') as YearMonth2,
	 // Date(monthstart(TempDate), 'YYYYMMDD') As YearMonth,
	 WeekDay(TempDate) as WeekDay;

//=== Generate a temp table of dates ===
LOAD Date(mindate + IterNo()) AS TempDate,
	 maxdate // Used in InYearToDate() above, but not kept
While mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD Min(Date#(FieldValue('DateKey', RecNo()), 'YYYYMMDD'))-1 as mindate, //'20170101' as mindate,
	 Max(Date#(FieldValue('DateKey', RecNo()), 'YYYYMMDD')) as maxdate // '20180101' as maxdate
AutoGenerate FieldValueCount('DateKey');

[Zaga_69]

Hallo Sunny,

I already found out why it did not work 🙂

Thank you for your support.

Best regards,

Edi

[sunny_talwar]

If you don't mind, would you be able to share why it did not work?

[Zaga_69]

The code I have posted now works (I assume the one you have posted also would work) 

My mistake was the order of the script. I had sth like this:

Main

Calendar

Hierarchy

Exit Script

Extract

Facts

I thought that since I already ran before the part below "Exit Script" it would works, however  to create the Master Calendar the "Fact" part should be place before the "Calendar" part:

Main

Facts

Calendar

Hierarchy

Exit Script

Extract

[sunny_talwar]

Got it and make sense.