topic Re: How to join multiple tables to avoid circular joins in App Development

How to join multiple tables to avoid circular joins

Qlik_Learner11 — Wed, 02 Oct 2024 00:01:50 GMT

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.

Re: How to join multiple tables to avoid circular joins

KJM — Wed, 02 Oct 2024 08:17:32 GMT

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 🙂

Re: How to join multiple tables to avoid circular joins

Qlik_Learner11 — Wed, 02 Oct 2024 23:53:16 GMT

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.