I have a table with the following fields:
Then I have a second table that has similar content:
Finally, there is a calendar-table (MASTER_CAL) with a date.
I joined the tables in load-script like:
AUTONUMBER(Upper(Text(TABLE1.Date)), '%MASTER_CAL') as %MASTER_CAL
AUTONUMBER(Upper(Text(TABLE1.OrgNo)), '%ORGNO') as %ORGNO
AUTONUMBER(Upper(Text(TABLE2.Date)), '%MASTER_CAL') as %MASTER_CAL
AUTONUMBER(Upper(Text(TABLE2.OrgNo)), '%ORGNO') as %ORGNO
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? 😩
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?
You can apply link table concept refer link for more details :
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.