topic Re: Master Calendar Problem in QlikView

Master Calendar Problem

Fri, 11 Sep 2015 19:23:44 GMT

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;

Re: Master Calendar Problem

dclark0699 — Fri, 11 Sep 2015 19:41:09 GMT

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

Re: Master Calendar Problem

MK_QSL — Fri, 11 Sep 2015 19:45:11 GMT

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..

Re: Master Calendar Problem

Fri, 11 Sep 2015 19:49:16 GMT

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]

Re: Master Calendar Problem

agni_gold — Fri, 11 Sep 2015 19:50:26 GMT

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 .

Re: Master Calendar Problem

Fri, 11 Sep 2015 19:52:56 GMT

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

Re: Master Calendar Problem

MK_QSL — Fri, 11 Sep 2015 19:54:03 GMT

What is the Original Format of OrderDate from your baseData?

Re: Master Calendar Problem

Fri, 11 Sep 2015 19:56:06 GMT

2002-03-25 07:26:41.000

Re: Master Calendar Problem

MK_QSL — Fri, 11 Sep 2015 19:56:53 GMT

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..

Re: Master Calendar Problem

MK_QSL — Fri, 11 Sep 2015 20:01:31 GMT

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;

Re: Master Calendar Problem

jonas_rezende — Sat, 12 Sep 2015 17:52:57 GMT

Hi, Colter Sherley.

The my bad experience was similar. I solved with use of MakeDate() function. Try like below:

Temp:

Load

min(MakeDate(Your([OrderDate]),Month([OrderDate]),Day([OrderDate]))) as minDate,

max(MakeDate(Your([OrderDate]),Month([OrderDate]),Day([OrderDate]))) as maxDate

Resident eComOrders;

Regards,

Jonas Melo.

Re: Master Calendar Problem

sasiparupudi1 — Sat, 12 Sep 2015 18:44:19 GMT

Change your formatting to below

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

Your master calendar loop only works for generating dates between a given minimum and a maximum date values

hth

Sasi

Re: Master Calendar Problem

Mon, 14 Sep 2015 15:44:02 GMT

Okay most of the solutions you guys provided gave me my minimum and maximum dates and fixed my original error so thank you all! After fixing that I ran into another problem. In a straight table my dates and times weren't grouping by the minute so I had to use the formula below to get rid of the seconds in my timestamp. Is there a better way to do this than the formula below? The formula is working fine, but i was just curious.

TIMESTAMP(FLOOR([OrderDate]*1440)/1440) AS [OrderDate]

Re: Master Calendar Problem

marcus_sommer — Mon, 14 Sep 2015 17:13:29 GMT

Maybe it's better to use both a master-calendar and a The Master Time Table.

- Marcus

Re: Master Calendar Problem

MarcoWedel — Mon, 14 Sep 2015 22:40:52 GMT