Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Need help implementing Master Calendar


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;

6 Replies
MVP
MVP

Re: Need help implementing Master Calendar

Shouldn't second line reference MaxDate?

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

Not applicable

Re: Need help implementing Master Calendar

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

MVP
MVP

Re: Need help implementing Master Calendar

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?

Not applicable

Re: Need help implementing Master Calendar

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

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

vikasmahajan
Esteemed Contributor

Re: Need help implementing Master Calendar

Hii,

MAcro Please find script here SIMPLE MASTER CALENDER

for creating master calendar.

hope this may help you

Vikas