topic Re: Need help implementing Master Calendar in QlikView

Need help implementing Master Calendar

Wed, 11 Jun 2014 19:29:46 GMT

I've just about exhausted all alternatives for implemnting master calendar and could really use some help. I've read all of the discussions very carefully and my results are still not correct. Can someone help me out?

When I create list boxes for the various date fields, there is no resulting lists. For example for months I only get a single month, and if I select it it doesnt highlight the appropriate data.

Here is my load script:

[event_fact]:

LOAD user_sk,
date_day_sk,
Date#(date_day_sk,'YYYYMMDD') as [Period Date]
FROM
event_fact.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

I'm creating the [Period Date] field from the date_day_sk which is a surrogate key representing a date but it is a string value. String would be '20140514' which is 'YYYYDDMM' format

Here is the Master Calendar Code:

Temp_Calendar_Range:

LOAD
min(num([Period Date])) as MinDate,
max(num([Period Date])) as MaxDate
RESIDENT [event_fact];

Let vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');
Let vMaxDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

DROP Table Temp_Calendar_Range;

[Master Calendar]:

LOAD DISTINCT
Year(Temp_Date) * 100 + Month(Temp_Date) as [Period Date],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
Date(Temp_Date, 'YYYY-MM') as [Year-Month],
'Q' & Ceil(Month(Temp_Date) /3) as [Quarter]
;
LOAD DISTINCT
$(vMinDate) + Iterno()-1 As Temp_Date
AUTOGENERATE(1)
WHILE $(vMinDate) + IterNo()-1 <= $(vMaxDate);
Let vMinDate = null;

Let vMaxDate = null;

Re: Need help implementing Master Calendar

swuehl — Wed, 11 Jun 2014 19:34:47 GMT

Shouldn't second line reference MaxDate?

Let vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');
Let vMaxDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

Re: Need help implementing Master Calendar

Wed, 11 Jun 2014 21:05:24 GMT

Yes. Sorry. Fixed that but that isnt the main issue.

Re: Need help implementing Master Calendar

swuehl — Wed, 11 Jun 2014 21:28:23 GMT

What is the value of both variables when running the script to that point?

What is the content of the first few decords of your master calendar? Could you upload a small sample?

Re: Need help implementing Master Calendar

Wed, 11 Jun 2014 21:51:51 GMT

I was able to figure it out I needed to format the dates in the fact and the calendar as date to with matching formats and that did the trick.

Re: Need help implementing Master Calendar

MarcoWedel — Wed, 11 Jun 2014 22:56:13 GMT

could you please post the result?

I'm interested in the formatting code that changes this expression

Year(Temp_Date) * 100 + Month(Temp_Date) as [Period Date]

into a date.

thanks

regards

Marco

Re: Need help implementing Master Calendar

vikasmahajan — Thu, 12 Jun 2014 05:14:05 GMT

Hii,

MAcro Please find script here SIMPLE MASTER CALENDER

for creating master calendar.

hope this may help you

Vikas