Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 tables like mentioned below:
Tab1:
Load
SubsGroupID,
PAYED_AMMOUNT,
...
....
Select SubsGroupID, PAYED_AMMOUNT, .... from SUBSCRIBERS_BONUS;
Tab2:
Load
SubsGroupID,
PAYED_AMMOUNT,
ATTRIBUTED_BONUS
Select SubsGroupID, PAYED_AMMOUNT, ATTRIBUTED_BONUS from SUBS_BONUS_CONFIGURATION;
The second table aims to keep things configurable, a kind of mapping between the subscriber group Id ( SubsGroupID) ,the payed ammount (PAYED_AMMOUNT) and the attributed bonus (ATTRIBUTED_BONUS).
When I load data I got wrong statistics, and when I checked on the net I found that it is due to Synthetic key. I found also that the problem appears only when we have more than one column to be joined (SubsGroupID,PAYED_AMMOUNT).
I should keep the table SUBS_BONUS_CONFIGURATION separated in order to update it.
Any body has an idea on how we can make join on multiple columns?
Thank you in advance!
Hi, you can make your own key, just create a link table with that key, try the following script :
Tab1:
Load
Autonumber(SubsGroupID,PAYED_AMMOUNT) as key,
SubsGroupID,
PAYED_AMMOUNT,
...
....
Select SubsGroupID, PAYED_AMMOUNT, .... from SUBSCRIBERS_BONUS;
Tab2:
Load
Autonumber(SubsGroupID,PAYED_AMMOUNT) as key,
SubsGroupID,
PAYED_AMMOUNT,
ATTRIBUTED_BONUS
Select SubsGroupID, PAYED_AMMOUNT, ATTRIBUTED_BONUS from SUBS_BONUS_CONFIGURATION;
T_key:
Load
key,
SubsGroupID,
PAYED_AMMOUNT
Resident Tab1;
Load
key,
SubsGroupID,
PAYED_AMMOUNT
Resident Tab2;
Key:
Noconcatenate Load distinct
key,
SubsGroupID,
PAYED_AMMOUNT
Resident T_key;
Drop field SubsGroupID,PAYED_AMMOUNT from Tab1;
Drop field SubsGroupID,PAYED_AMMOUNT from Tab2;
Drop table T_key;
It is always a best practice to resolve all synthetic keys in your script. Qlikview will create one for every combination, which can result in incorrect values. Another best practice is to include in the name of the key that you are creating an identifier of some sort so that you will always know that it is something that you've created versus from your data source. Another good idea is to use numbers or integers rather than text to speed up the connections.
If the tables have a 1 to 1 or 1 to many cardinality with no optionals on the 1 side, then you do not need to create an extra table. In this example, Table 1 is the one and Table 2 is the 0 to many in the cardinality:
Table1:
SQL Select
SubsGroupID,
Payed_Amount,
SubsGroupID + Payed_Amount AS '%Subscribers_Bonus_Key',
...
From SUBSCRIBERS_BONUS;
Table2:
SQL Select
Attributed_Bonus,
SubsGroupID + Payed_Amount AS '%Subscribers_Bonus_Key',
...
From SUBS_BONUS_CONFIGURATION;
This assumes that all combinations of Table2's key exists in Table1.
In the following example, the cardinality includes either an optional to many or many to many cardinality. This will require another table.
Table1:
SQL Select
SubsGroupID,
Payed_Amount,
SubsGroupID + Payed_Amount AS '%Subscribers_Bonus_Key',
...
From SUBSCRIBERS_BONUS;
Table2:
SQL Select
SubsGroupID,
Payed_Amount,
Attributed_Bonus,
SubsGroupID + Payed_Amount AS '%Subscribers_Bonus_Key',
...
From SUBS_BONUS_CONFIGURATION;
LINK_Table:
LOAD
SubsGroupID,
Payed_Amount,
%Subscribers_Bonus_Key
Resident Table1;
Outer Join (LINK_Table)
LOAD
SubsGroupID,
Payed_Amount,
%Subscribers_Bonus_Key
Resident Table2;
DROP Fields
SubsGroupID,
Payed_Amount
From Table1,Table2;
This pulls in every combination and makes sure that it only joins correctly while resolving the synthetic key.
Thanks,
Aline
Hi,
Thanks for your replies, they are so helpful :))