Two fact tables with different date granularity join to a single calendar

[#1]

hi qlikers,

Need help on this one.

Originally i had 1 fact table joined to a single master calendar table on key_CalendarDay. It looks something like this.

OppNo	Value	keyCalendarDay
ABC1	20000	1001
DFS2	3400	1002
FGS3	450	1003
JHU4	500	1004

And my master Calendar table :

keyCalendarDay	keyCalendarWeek	keyCalendarMonth	keyCalendarQtr	ProcessDate	YYYYWK	YYYYMM	YYYYQ	YYYY
1001	10001	100001	1000001	01-01-2014	201401	201401	20141	2014
1002	10001	100001	1000001	01-02-2014	201401	201401	20141	2014
1003	10001	100001	1000001	01-03-2014	201401	201401	20141	2014
1004	10001	100001	1000001	01-04-2014	201401	201401	20141	2014
1005	10002	100001	1000001	01-05-2014	201402	201401	20141	2014

Now I would need to pull in another fact table, but this time it has a different date granularity , something like this.

OppNo	ForecastValue	keyCalendarQtr
ABC1	123000	1000001
DFS2	201000	1000001
FGS3	230000	1000001
JHU4	120330	1000001
ABC1	200000	1000002
DFS2	300000	1000002
FGS3	400000	1000002
JHU4	200010	1000002

So the first and second table is joined on key_CalendarDay.

Now i would need to bring in the forecast table (Table3), and link it to the calendar table on keyCalendarQtr.

Any ideas on how I could pull this off ? Do I need to use a link table for this ? if so, how would i do it ?

Thanks.

Shan.

[its_anandrjs]

Update script now you can check

If you left join your Fact tables and take it into single fact table on the basis of the OppNo field because it is present in both tables. And then create a composite keys based on the keyCalendarDay&'_'&keyCalendarQtr as %Key  and also create this key in the Calendar table then your fact table get connected with the calendar.See the script for this

Temp_Fact:

LOAD * INLINE [

    OppNo, Value, keyCalendarDay

    ABC1, 20000, 1001

    DFS2, 3400, 1002

    FGS3, 450, 1003

    JHU4, 500, 1004

];

Left Join(Temp_Fact)

LOAD * INLINE [

    OppNo, ForecastValue, keyCalendarQtr

    ABC1, 123000, 1000001

    DFS2, 201000, 1000001

    FGS3, 230000, 1000001

    JHU4, 120330, 1000001

    ABC1, 200000, 1000002

    DFS2, 300000, 1000002

    FGS3, 400000, 1000002

    JHU4, 200010, 1000002

];

FinalFact:

LOAD keyCalendarDay&'_'&keyCalendarQtr as %Key,OppNo,Value,ForecastValue Resident Temp_Fact;

DROP Table Temp_Fact;

Cal:

LOAD *,keyCalendarDay&'_'&keyCalendarQtr as %Key;

LOAD * INLINE [

    keyCalendarDay, keyCalendarWeek, keyCalendarMonth, keyCalendarQtr, ProcessDate, YYYYWK, YYYYMM

    1001, 10001, 100001, 1000001, 01-01-2014, 201401, 201401

    1002, 10001, 100001, 1000001, 01-02-2014, 201401, 201401

    1003, 10001, 100001, 1000001, 01-03-2014, 201401, 201401

    1004, 10001, 100001, 1000001, 01-04-2014, 201401, 201401

    1005, 10002, 100001, 1000001, 01-05-2014, 201402, 201401

];

];