Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thomasmercer
Contributor III
Contributor III

Circular reference with time based data

Read a lot about circular references but still stuck on a problem.

There's a lot of tables in the model but essentially the issue is between three tables:

1. Extracted Job Data - this includes:

  • Database Code
  • Month (inferred from a timestamp)
  • Underlying data for measures

2.  FTE Breakdown- this includes:

  • Model - This might be model A or model B 
  • Month
  • Number of team members

3. Fund mapping - this includes:

  • Database Code;
  • Model - This can be model A or model B

I figure I can deal with the max (both) model using set analysis but I can't figure out how to get my joins right:

Cicular reference.jpg

 

Some notes on the links:

  1. There are several dozen DB codes which fall into a Model;
  2. The Models are how the FTE breakdown is categorised;
  3. FTE breakdown month is directly related to the months in which the Extracted Job Data falls into

Been scratching my head over this for too long now. Any thoughts of how I can fix?

Labels (1)
5 Replies
Lisa_P
Employee
Employee

you must either join some tables or create a link table to handle the relationship between common fields. It is hard to say with just this snapshot of the data
thomasmercer
Contributor III
Contributor III
Author

Hi Lisa,

I'm not sure how I'd use a link table unless I did something like Month-DBCode, which would be really difficult to maintain.

Is there more information I can provide to help better explain the problem?

Thanks,

Thomas

Lisa_P
Employee
Employee

The problem with circular references is that because the Qlik Sense engine is always trying to understand the relationships in the data, this scenario may give ambiguous results and therefore one of the tables will become loosely coupled and may result in incorrect results in your visualizations. Therefore this ambiguity must be resolved.

thomasmercer
Contributor III
Contributor III
Author

Hi Lisa,

Thanks for your response.

I've through a lot on circular references as it is a common issue in the forums but I still am having trouble figuring out how to actually setup my schema in scalable way.  The only options I can think of at the moment are:

1. Create a DBCode-Month link table; or

2. Output model as a field in the Extracted Job Data dataset

I'm hoping there's a better solution available than either of the above.

Harris90
Contributor
Contributor

Agreed....Qlik Sense engine is always trying to understand relationships in data..Can't agree more than it