Generic Keys with 2 Fact Tables (weekly/daily data)

[Milo2009]

Hi everybody!

I have some struggle to create a data model which fits to my needs. Basically I have two fact tables - one at a daily level (tab DailyData) and another one on a weekly level (see the tab PromotionData).
In the promotion data the week results from the column promotion: 2000 is week 2, 48000 would be week 48, etc.

In my applications I regulary use a MasterCalender. But in this case, I dont know how to implement it in a good way. I dont want seperate Year, YearWeek or Week fields for every fact table. So I have searched a bit and come across the concept of 'generic keys'. Although I have read the  Doc, I'm struggeling with the adaption to my problem/data.

It would be great, if someone can push me in the right direction (or suggest a better idea).

Thanks in advance,

Milo

[Stoyan_Terziev]

Hi Milo,

Why not concatenate the two fact tables into one master fact table and create Master Calendar which is linked to the Fact Data by the Week field.

Like so:

You can resolve your problem in multiple ways, but that one to me seems to be the easiest.

Let me know if this helps.

Kind regards,

S.T.

[Brett_Bleess]

In addition to Stoyan's post, check out the following Design Blog post, it may provide another idea:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

If you want to do some further searching in this area on your own, use the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett