Common time axis for different Dimensions

alliedarmour — Fri, 20 Oct 2023 23:29:20 GMT

Hello!

So, I'm trying to solve the following problem: I have 3 tables which I need to bring together in a type of master calendar. Per user requirement, these three should have one (common) selectable time axis (month and year).

Table 1 and 2 contain the same type of key, but table 2 is missing some keys that exist in table 1. Table 3 has an enitrely different jey. Table 2 contains keys from Table 1 and Table 2.

So, my idea was to build a "semi master calendar" for those tables by concatenating them and use their time attributes with a common name like this:

MasterKalender:
Load Distinct
Klinik_FA_Key,
Null() As CO_VK_Key,
Entlassungsmonat As Month,
Entlassungsjahr As Year
Resident T1;

Concatenate (MasterKalender)
Load Distinct
Klinik_FA_Key,
CO_VK_Key,
Periode As Month,
Geschäftsjahr As Year
Resident T2;

Concatenate (MasterKalender)
Load Distinct
Null() As Klinik_FA_Key,
CO_VK_Key,
Personal_Monat AS Month,
Personal_Jahr As Year
Resident T3;

As you see, only table 2 contains both key fields without either being Null().

This approach is kinda working (numbers seem to be correct), but creates a synthetic key cause now the master calendar and T2 contain the same field-/keyset:

Synthethic Key

I can't delete them from T2 cause I need them as relations to Table 1 and Table 3, but neither I can delete them on the master calendar.

Is there any other solution or option without a synthetic key? I know they're not necessarily a bad thing but I rather tend to avoid them, if possible.

Any idea what I could do to fix this?

Thanks and have a great weekend!

Re: Common time axis for different Dimensions

Mark_Little — Sat, 21 Oct 2023 07:36:11 GMT

Hi,

I think you have the right thought, take a read through this helps with this sort of calendar.

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

Re: Common time axis for different Dimensions

alliedarmour — Sat, 21 Oct 2023 08:24:17 GMT

Hey Mark,

I already considered usiing a canonical date, but this seems to work only when all three tables share the same key - which, unfortunately, is not the case 🥲

topic Re: Common time axis for different Dimensions in App Development

Common time axis for different Dimensions

Re: Common time axis for different Dimensions

Re: Common time axis for different Dimensions