Master Calendar Problem

[#1]

Hello Qlik Community,

I'm having a little bit of trouble with my master calendar script. I've reused this script in many of my applications without any trouble. Today I was developing a new application and it threw a script error after I inserted the master calendar code. A picture of the error is attached and my code is below. I don't understand why Qlik thinks there is a field in this part of the script. Has anyone ran into something similar to this? Thanks in advance!

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & if (Ceil (rowno()/3) = 4, 1, Ceil (rowno()/3) + 1) as Quarter

AUTOGENERATE (12); 

Temp: 

Load 

               min([OrderDate]) as minDate, 

               max([OrderDate]) as maxDate 

Resident eComOrders; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               TempDate AS CalendarDate, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,    

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

[dclark0699 · Accepted solution]

Looks like $(varMinDate)  and $(varMaxDate) are not returning anything. Are you sure

Temp:

Load

               min([OrderDate]) as minDate,

               max([OrderDate]) as maxDate

Resident eComOrders;

is returning values?

Try exiting your script right after that and viewing the Temp table

[MK_QSL]

Looks like your variables varMinDate and varMaxDate are not getting created.

You are creating them using OrderDate and giving date name as CalendarDate in master calendar. Check is that OK or not?

Create a text box and write =Min(OrderDate),if you are not getting number, means you have date formatting issue which is not allowing to create variables..

[#4]

Yes you're both right! I just ran the debugger step by step and it showed that my varMinDate and my varMaxDate were null. I also did min(OrderDate) and it came out as "-".

I had to do some formatting to the date so I bet that's why it doesn't like it. The formatting of the date is below.

Date#(DATE([OrderDate],'M/D/YYYY h:mm:00')) AS [OrderDate]

[agni_gold]

Hi Friend,

I think your order date format is not correct . Convert into number first

Date#(Order_Date,'DD-MM-YYYY')  AS Order_Date and then use in your calendar script.

Please see attached file .

[#6]

Is there a way that I can keep the time formatting in there? I need the hours and minutes but not the seconds.

[MK_QSL]

What is the Original Format of OrderDate from your baseData?

[#8]

2002-03-25 07:26:41.000

[MK_QSL]

TimeStamp(Left(TimeStamp#(OrderDate,'DD/MM/YYYY hh:mm:ss'),16),'DD/MM/YYYY hh:mm') as OrderDate

You can use

Min(Date(Floor(TimeStamp#(OrderDate,'DD/MM/YYYY hh:mm')))) as MinOrderDate

and same way Max also..

[MK_QSL]

Use below to find min and max OrderDate

Temp:

Load

               Min(Date(Floor(Timestamp#(OrderDate,'YYYY-MM-DD hh:mm:ss.fff')))) as minDate,

               Max(Date(Floor(Timestamp#(OrderDate,'YYYY-MM-DD hh:mm:ss.fff')))) as maxDate

Resident eComOrders;