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