How to avoid loops in this date link example...

[#1]

Hello,

I'm in the process of testing a new data structure which may work for my team.

I have a main job table with multiple date fields (lead date, survey date) each with its own fact field (lead qty, survey qty).

Obviously joining these dates to a master calendar table direct would create synthetic keys which I am trying to avoid. Instead I have created some intermediate link tables - "LinkTableLead" and "LinkTableSurvey" which contain the respective date field (which associate with the main job table) plus the calendar date (which associates with the master calendar table).

The problem is this is creating loops with the following error - "One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog"

How can I avoid this?

I am trying to create a data structure where I can select a calendar date/week/month and it filter the correct facts dependant on their individual dates.

Any help would be appreciated here.

I have attached the qvw.

[Gysbert_Wassenaar]

I think I'd use two calendars in this case. One for each date field. Since both dates are in the same table link tables are not an option I think. You can also use a date island in combination with a OnSelect trigger and Select in Field actions if you want to choose a date and select the surveys where both lead and survey date are the same as the selected date.

[#3]

Thanks for the reply.

Sorry should have pointed out in the initial post - in reality my main job table has 4 or 5 different "facts" all with different dates attached to them so I'm not sure using 5 different calendars would be a preferred option here.

[Gysbert_Wassenaar · Accepted solution]

See here then

[#5]

Thanks for your help.

Looks like I need to seperate all my facts and their dates into individual tables rather than they all exist in a "main job" table. Once I've done this, this example should work perfectly.