Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
reporting_neu
Creator III
Creator III

How to avoid synthetic key

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? 😩

Labels (2)
4 Replies
Mark_Little
Luminary
Luminary

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
Creator III
Creator III
Author

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
reporting_neu
Creator III
Creator III
Author

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.Unbenannt.png