Calendar Script issue

[Anonymous]

All

I am using the below script to create a master calendar

Load [Off Site Date], Month([Off Site Date]) as Month1, Year([Off Site Date]) as Year;

Load Date(MinDate+iterno()) as Date While iterno() <=MaxDate - MinDate;

Load Min([Off Site Date])-1 as MinDate, Max([Off Site Date]) as MaxDate Resident All_Activity_Nationals$

When I run, I get the following error

I don't understand as the [Off Site Date[ field loads correctly from my main script and gives no issues.

I've double and triple checked the field name, table name, everything without success.

What am I doing wrong?!

[Digvijay_Singh]

I think you need to use 'Date' in the last load( top one). You are not carrying upward your OffSite date so it is not available in further loads, but I think it is not required in this context as 'Date' is available after second load and should be used to define Month and Year.

[teiswamsler]

Hi Jamie

Change this:

Load [Off Site Date], Month([Off Site Date]) as Month1, Year([Off Site Date]) as Year;

Load Date(MinDate+iterno()) as Date, While iterno() <=MaxDate - MinDate;

Load Min([Off Site Date])-1 as MinDate, Max([Off Site Date]) as MaxDate Resident All_Activity_Nationals$

or use this

Load [Date], Month([Date]) as Month1, Year([Date]) as Year;

Load Date(MinDate+iterno()) as Date, While iterno() <=MaxDate - MinDate;

Load Min([Off Site Date])-1 as MinDate, Max([Off Site Date]) as MaxDate Resident All_Activity_Nationals$

/Teis

[Digvijay_Singh]

Check this out, normally used for calendar creation, you can load min date and max date from your fact table also.

Let vMinDate = Num(Date#('2014-01-01','YYYY-MM-DD'));

Let vMaxDate = Num(Date#('2015-10-31','YYYY-MM-DD'));

MasterCalendar:

Load Date(TempDate,'YYYY-MM-DD') as Date,

  Day(TempDate) As Day,

  Month(TempDate) as Month,

  Year(TempDate) as Year,

  'Q' & Ceil(Month(TempDate)/3) as Quarter;

//This autogenerates the dates starting from Min date to Max date having no of records equal to (Max Date - Min Date)

Load  num#($(vMinDate)) + Iterno() -1 as TempDate

AutoGenerate(1)

  While num#($(vMinDate)) + Iterno() -1<= num#($(vMaxDate));

[Gabriel]

Hi,

Kindly attach sample data

[maxgro]

from my test your script (bold) work if you have  All_Activity_Nationals$ with an [Off Site Date] field

All_Activity_Nationals$:

load date(makedate(2015)+rowno()-1) as [Off Site Date]

AutoGenerate 730;

Load [Date], Month([Date]) as Month1, Year([Date]) as Year;

Load Date(MinDate+iterno()) as Date While iterno() <=MaxDate - MinDate;

Load Min([Off Site Date])-1 as MinDate, Max([Off Site Date]) as MaxDate Resident All_Activity_Nationals$;