How to join multiple tables to avoid circular joins

[Qlik_Learner11]

Hello Experts,

I have benefited from you multiple times in the past. I request you to please help me this time too.

There are 3 tables revenue, customer and cust_privilege. you can see the fields in each of the tables below. There are more fields in the actual tables but I am skipping them for the context.

revenue

tkt_key

tkt_nbr

tkt_iss_date

tkt_price

cust_key

customer

cust_key

cust_name

cust_id

cust_privilege

tkt_nbr

cust_id

cust_status

revenue and customer tables are already being loaded in the script. Now I need to add cust_privilege table to the script. revenue table is associated with customer table on cust_key field. So, if I select tkt_nbr, all the associated customer names will be filtered in the list box.

My requirement is when I click a customer name in the list box, it's associated cust_status from cust_privilege table should be filtered. To achieve this I am not sure how to join the cust_privilege table with already existing revenue and customer tables. If I join the cust_privilege table with revenue table on tkt_nbr, selecting the customer name would filter all the cust_ids associated with the tkt_nbr. And If I join the cust_privilege table with customer table on cust_id, then selecting customer name would filter the cust_id and cust_status but for all tkt_nbrs. Please note cust_status value is different for each tkt_nbr and cust_id combination. To get the cust_status for a customer, we need both tkt_nbr and cust_id combination.

Joining cust_privilege table to revenue table on tkt_nbr and joining cust_privilege table to customer table on cust_id will create circular join which is not recommended.
I am not sure how to join all these tables so that when I select a customer name, it should filter all the ticket numbers, and when i select a ticket number, and it should filter the associated cust_id and cust_status. I hope I was able to explain the problem in detail.

Please someone suggest a solution. Thanks so very much in advance.

[KJM]

Hi Qlik Learner!

Could you use a Join to add cust_name and cust_id to the revenue table based on the cust_key field and then use a link table to avoid synthetic keys on the remaining two tables?  There are tutorials on youtube on link tables but I think I'd do something like the following (albeit I would prefer to play about with the app to be sure!)

#left join the customer fields to the revenue and drop customer from the model
Left Join (revenue)
Load * Resident customer;
Drop Table customer;

#create a link key for the link table in each table
revenue_withkey:
Load
    *,
    [tkt_nbr] & [cust_id] as linkKey
Resident revenue;
Drop Table revenue;

cust_privilege_withkey:
Load
    *,
    [tkt_nbr] & [cust_id] as linkKey
Resident cust_privilege;
Drop Table cust_privilege;

#create the link table and drop the fields from the originals
LinkTable:
Load distinct*;
Load
    linkKey,
    [tkt_nbr],
    [cust_id]
Resident revenue_withkey;

Load
    linkKey,
    [tkt_nbr],
    [cust_id]
Resident cust_privilege_withkey;

drop fields [tkt_nbr], [cust_id] from revenue_withkey,cust_privilege_withkey; 

I'm sure that's not perfect.  It might need a NoConcatenate somewhere.  But I think its nearly there in terms of what you need.  If this helps please like and mark as solution 🙂

K.

[Qlik_Learner11]

Hi KJM,

Thank you so much for quick reply and possible solution. Unfortunately I can't join revenue and customer tables. This is an existing app and those two tables are associated using Keep. But I think I could follow your solution. I guess I need to create another table and pull tkt_nbr and cust_id fields into it and create a link key. And then create the same key and import only the other fields from cust_privilege table. I am not sure if I will be able to do that. If you think it won't work please let me know. Thank you again for your help.