Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Mixed Granularity Keys Associating Single Table To Master Calendar

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?

1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

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.

View solution in original post

1 Reply
Highlighted
Contributor II
Contributor II

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.

View solution in original post