topic Re: Working with a Global Master Calendar in QlikView

Working with a Global Master Calendar

hobanwashburne — Wed, 28 May 2014 14:51:19 GMT

I have generated a Master Calendar, but I an somewhat confused as to how to Join tables having multiple important dates.

Say a customer order table contains:

Order Entry Date (20140528)

Customer Requested Delivery Date (20140530)

Actual Ship Date (20140602)

My Master Calendar contains:

Year (2013, 2014,...)

YearMonth (201404, 201405, ...)

YearMonthDay (20140527, 20140528,...)

Do I need to repeat YearMonthDay three times?

My new master calendar would now look like:

Year (2013, 2014, ....)

YearMonth (201404, 201405,...)

YearMonthDayEntryDate (20140527, 20140528, ...)

YearMonthDayReqDate (20140527, 20140528,...)

YearMonthDayShipDate (20140527, 20140528,...)

Or is there a better way?

Re: Working with a Global Master Calendar

bbi_mba_76 — Wed, 28 May 2014 15:31:00 GMT

Hi,

maybe those posts are useful: http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

Using Multiple Dates With Master Calendar

Re: Working with a Global Master Calendar

hic — Wed, 28 May 2014 15:39:14 GMT

Also, look at Canonical Date.

HIC

Re: Working with a Global Master Calendar

hobanwashburne — Wed, 28 May 2014 17:05:19 GMT

HIC

This is exactly that I was looking for, however I am running into some trouble getting it to work with my data. Imagine that all three dates (Shipped Date, Order Date, & Required Date) all exist in the Order Lines table. As I repeat the:

Load OrderLineID, ShippedDate as CanonicalDate, 'Shipped' as DateType

resident OrderLines;

Load OrderLineID, OrderDate as CanonicalDate, 'Order' as DateType

resident OrderLines;

I end up with Synthetic Keys