topic join two tables on multiple columns in QlikView

join two tables on multiple columns

Tue, 08 Mar 2011 18:32:33 GMT

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!

join two tables on multiple columns

Wed, 09 Mar 2011 18:07:46 GMT

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;

join two tables on multiple columns

Wed, 09 Mar 2011 19:05:53 GMT

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

join two tables on multiple columns

Thu, 10 Mar 2011 08:51:01 GMT

Hi,

Thanks for your replies, they are so helpful :))