Working with a Global Master Calendar

[hobanwashburne]

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?

[bbi_mba_76]

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

[hic]

Also, look at Canonical Date.

HIC

[hobanwashburne]

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