qlik filter for multiple tables

[saikripa]

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. 

[MayilVahanan]

HI Sai,

Please go through the link, which helps you.

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

[saikripa]

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.

[gmenoutis]

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.

[saikripa]

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

[gmenoutis]

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?