Mixed Granularity Keys Associating Single Table To Master Calendar

[acholchemit]

Hi,

I have two tables that I concatenated into one table.

The first table contains data like the following

Posting Date, Category, Amount

01/04/2020, A, 500

05/05/2020, B, 200

The second table contains the following information

Year-Month, Category, Target

2020-04, A, 1000

2020-05, A, 4000

2020-04, B, 100

2020-05, B, 50

I have used the dates in the first part of the table to associate to a master calendar.  However, I need the second part of the concatenated table to also associate to the master calendar at the year-month granularity.

I am struggling to see how I do this, any ideas?

[acholchemit · Accepted solution]

I've been able to figure this out.

I created a Bridge table between the Original table and the Master Calendar.  In this I loaded all of the original key values and then an aliased version of the key that was then used to link back to the master calendar breaking the original link.  I then created a year month field in the correct format in the master calendar.  This field was then used as a key by concatenating the master calendar into the bridge table using this field but with the same name as the other key field.

Bridge Table Script below.

In the concatenated table (data table) that links to the bridge create the link field using either the date or the yearmonth.  Ensure that bridge and the master calendar have the link at the date level and it all works.

Master_Calendar_Bridge:
// This table bridges the master calendar to the Master Dimensions and allows a lower level of granularity in the association e.g. Year-Month as well as by individual date

LOAD
[KEY HASH128 Bridge Date] AS [KEY HASH128 Entry Date], // Key to Full Date Fields
[KEY HASH128 Bridge Date] AS [KEY HASH128 Bridge Date] //Key back to Master Calendar

Resident EntryMasterCalendar;

CONCATENATE LOAD

HASH128([Entry Bridge YearMonth]) AS [KEY HASH128 Entry Date], // Key to Year-Month Date Fields
[KEY HASH128 Bridge Date] AS [KEY HASH128 Bridge Date] //Key back to Master Calendar

RESIDENT EntryMasterCalendar;

Hope this helps someone else who has the same issue in the future.