topic Calendar Script issue in QlikView

Calendar Script issue

Anonymous — Wed, 14 Oct 2015 10:41:11 GMT

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?!

Re: Calendar Script issue

Digvijay_Singh — Wed, 14 Oct 2015 10:50:44 GMT

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.

Re: Calendar Script issue

teiswamsler — Wed, 14 Oct 2015 10:52:26 GMT

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

Re: Calendar Script issue

Digvijay_Singh — Wed, 14 Oct 2015 10:54:26 GMT

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));

Re: Calendar Script issue

Gabriel — Wed, 14 Oct 2015 11:06:24 GMT

Hi,

Kindly attach sample data

Re: Calendar Script issue

maxgro — Wed, 14 Oct 2015 11:18:56 GMT

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$;