How to avoid synthetic key

[reporting_neu]

Hello,

I have a table with the following fields:

• OrgNo
• OrgDescription
• Date
• Valid

Then I have a second table that has similar content:

• Employees
• OrgNo
• Date
• Valid

Finally, there is a calendar-table (MASTER_CAL) with a date.

I joined the tables in load-script like:

Table1:
AUTONUMBER(Upper(Text(TABLE1.Date)), '%MASTER_CAL') as %MASTER_CAL
AUTONUMBER(Upper(Text(TABLE1.OrgNo)), '%ORGNO') as %ORGNO

Table2:
AUTONUMBER(Upper(Text(TABLE2.Date)), '%MASTER_CAL') as %MASTER_CAL
AUTONUMBER(Upper(Text(TABLE2.OrgNo)), '%ORGNO') as %ORGNO

MASTER_CAL:
AUTONUMBER(Upper(Text(MASTER_CAL.Date)), '%MASTER_CAL') as %MASTER_CAL

The problem is that later in Qlik I select a date from the MASTER_CAL. This is to check whether the data record in Table1 and Table2 is valid.

The two tables are connected via the OrgNo. That is right as well. However, there should be no connection between Table1 and Table2 via the key %MASTER_CAL. This is because a synthetic key is generated by Qlik. They should therefore be connected to the MASTER_CAL independently of one another.

How can I implement something like this? 😩

[Mark_Little]

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

You will need to address the Synthetic key first, by aliasing the the fields in one of the tables.

[reporting_neu]

Thank you for your answer and the link.

Unfortunately, the thread only talks about tables that have an "independent" date.

In my case, I want to select a master date later and the date from Table1 and Table2 must be exactly the same.

At the moment, Table1 and Table2 are also connected via the date and via the OrgNo and thus data is also excluded if a date is not available somewhere in the other table. And that's my problem right now.

Do you understand what I mean?

[vikasmahajan]

Hi ,

You can apply link table concept  refer link for more details :

https://community.qlik.com/t5/Qlik-Sense-Documents/link-table/ta-p/1883375

Vikas

[reporting_neu]

Also thank you for your answer.
Unfortunately, that doesn't work either.

I need to be able to select a date in Qlik because I want to see sick leave for this exact date, for example.

In Table1, the date must be used to identify the department in which the employee works. So whether he is assigned to the department on the exact date.

In Table2, the date must be used to identify whether the department is still valid at all or is perhaps no longer valid because a new one was added.

Please look picture.