3 Replies Latest reply: Mar 10, 2011 3:51 AM by hanen111 RSS

    join two tables on multiple columns

      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

          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

            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