topic Re: Generic Keys with 2 Fact Tables (weekly/daily data) in QlikView

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

Milo2009 — Sat, 16 Nov 2024 19:07:43 GMT

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

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

Stoyan_Terziev — Wed, 12 Feb 2020 15:05:32 GMT

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.

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

Brett_Bleess — Wed, 11 Mar 2020 13:29:13 GMT

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