topic Re: Joins with multiple columns in QlikView

Joins with multiple columns

Anonymous — Wed, 05 Aug 2015 17:23:31 GMT

Hi All,

I have 2 tables named Policy and Client.

I want to make a join between the above 2 tables based on 2 different columns from Policy and 1 column from Client table like

Client.Client_ID = Policy.First_User_Id and

Client.Client_ID = Policy.RO_Name_Id

But as we all know we can't have join the two tables based on 2 columns so I used this approach -

I created 2 additional column in each table like -

Policy Table -

Autonumber(PO_FIRST_USER_ID,[RO_NAME_ID]) as 'Client ID'

Client Table -

Autonumber(CL_CLIENT_ID,CL_CLIENT_ID) as 'Client ID'

Please suggest if this is the right approach or the above requirement can be implemented in some other way.

Re: Joins with multiple columns

Anonymous — Wed, 05 Aug 2015 20:53:39 GMT

your Approach would be okay, if Policy.First_user_id and Policy.RO_Name_id are always identical.

I donot know your Business, but Policys can have different persons (and therefor different ids). Insurance policys might have the insured Person and a Person who pays. If These persons are not identical you will not get a match.

An Approach might be to duplicate the Client table and Name Client id to RO_Name_id (which is for sure not best Approach as it takes additiional space)

Re: Joins with multiple columns

maxgro — Wed, 05 Aug 2015 21:19:09 GMT

also

Client.Client_ID & '|' & Client.Client_ID as key

Policy.First_User_Id & '|' & Policy.RO_Name_Id as key

Re: Joins with multiple columns

Anonymous — Thu, 06 Aug 2015 05:13:53 GMT

Thanks Rudolf for the answer and logic. Your logic seems to be correct we can have different FIRST_USER_ID and RO_NAME_ID and thus creating alias of Client table should be the suitable approach here.

Re: Joins with multiple columns

Anonymous — Thu, 06 Aug 2015 05:42:09 GMT

would this act same as what I implemented ?

Re: Joins with multiple columns

maxgro — Thu, 06 Aug 2015 07:57:34 GMT

yes, try, reload and open the table viewer (ctrl+t); you should see the 2 tables associated (join) by the field key