topic qlik filter for multiple tables in App Development

qlik filter for multiple tables

saikripa — Sat, 16 Nov 2024 17:23:37 GMT

Hello,

I am developing a qlik app , I have 4 tables (a,b,c,d) associated to 1 common table (F). table a,b,c, and d has date fields with different name. If I give alias to the dates in all the 4 tables , I am getting a circular reference error. I can't link these 4 tables with date for other good reasons. How can apply filter for the calculations based on the date in all 4 tables.

Re: qlik filter for multiple tables

MayilVahanan — Mon, 15 Mar 2021 08:25:36 GMT

HI Sai,

Please go through the link, which helps you.

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Re: qlik filter for multiple tables

saikripa — Mon, 15 Mar 2021 11:40:20 GMT

Hello Mayil Vahanan,

I went through the link and created a canonical date table , but my tables are already associated with one column as common for 4 and all these linked to the 5th table. Adding the canonical date table is again showing circular reference error. Below diagram shows my table associations.

Re: qlik filter for multiple tables

gmenoutis — Tue, 16 Mar 2021 10:38:03 GMT

Can you please provide the names (or example names) of the table names, date fields and the common field with table E so that we can try to make a sample code?

Actually, what will be needed also is the "key" of table E: a combination of E columns that have unique value for each row.

Re: qlik filter for multiple tables

saikripa — Tue, 16 Mar 2021 12:00:36 GMT

Table A and B are linked to Table E with orderID and compnay col as primary key.

Re: qlik filter for multiple tables

gmenoutis — Tue, 16 Mar 2021 12:51:34 GMT

So you are using the same fields (Order Id, Company) to connect both A and B to E. For the examples that follow, let's say your tables only have the rows of the screenshots.

Two options appear at this point:

1) Decide on a logic on date combinations.

EG1: An "or"/"union" logic returns all order id data if ANY date exists.

Example: You select 5/1/2020. Due to Table2Date, this returns AHD/DEF. Because of this, table1Codes 30,43,11 are also returned.

2) Make dedicated keys

If it is very important to only show lines of dates that correspond only to the selected value, you will have to change the connections to E (because the order Id can only be yes or no, not yes for one table and no for other). In this case, create in tables A,B... a new field that is unique for all rows of that table and then you can post here the new field names and we can help you make an intermediate join table that returns you what you want.

What do you think would work for your case?