topic Re: Help with Master Calendar in QlikView

Help with Master Calendar

Sat, 07 Jun 2014 12:37:49 GMT

I've been racking my brain with this one and I'm hoping someone can help. I've tried numerous ways to implement a master calendar, and I can get the Master calendar table created just fine but linking the FACT and Calendars seems not to be working.

The issue is when attempting to link the Master Calendar to my FACT table. The date in my FACT table is formatted as text 'YYYYMMDD' format.

Can someone post a simple example of a master calendar implementation with the appropriate handling for a text based key in the fact table?

Here is the code I've used to implement the master calendar It is missing the link betweeen fact and calendar tables:

SET STARTDATE = '20130101'; //fill in your start date
LET ENDDATE = date(today());

[TEMP_DATE]:
LOAD
date( date#('$(STARTDATE)','YYYYMMDD')-1 + recno() ,'YYYYMMDD') as DATE
AUTOGENERATE (date#('$(ENDDATE)') - date#('$(STARTDATE)'))+1;

[DATETABLE]:
LOAD
DATE,
Year(DATE) as YEAR,
Month(DATE) as MONTH,
Week(DATE) as WEEK,
WeekDay(DATE) as WEEKDAY,
Day(DATE) as DAY,
Year(DATE) & right('00' & week(DATE),2) as YYYYWW,
Year(DATE) & right('00' & num(Month(DATE)),2) as YYYYMM,
Year(DATE) & ceil(num(Month(DATE))/3) as YYYYK,
'Quarter' & ceil(num(Month(DATE))/3) as QUARTER

RESIDENT [TEMP_DATE];

DROP TABLE [TEMP_DATE];

Re: Help with Master Calendar

swuehl — Sat, 07 Jun 2014 12:52:18 GMT

Make sure that your dates in your FACT table are in fact dates, having a numeric representation, and that they are pure dates, not timestamps, just formatted as dates.

If they are indeed timestamps, use

LOAD

date(floor(DATE)) as DATE,

...

in your fact table load.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

Re: Help with Master Calendar

Sat, 07 Jun 2014 13:02:58 GMT

Is there a way to format them as dates when I load them in my script rather than changing in the database?

Re: Help with Master Calendar

swuehl — Sat, 07 Jun 2014 13:12:01 GMT

Sure, no need to change the database. Just follow the link and read Henrics blog post and technical brief.

QV dates (as returned e.g. by makedate(2014,6,7) ) are dual values:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/13/dual

For interpreting a string as a date, use Date#() function.

For formatting a date number as a date string, use Date() function.

Use floor() or daystart() to remove the time part of a timestamp's numeric representation.