Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

thomasmercer
New Contributor III

Circular reference through four tables

Trying to add a new dimensions and measure across an existing data set but I can't for the life of me figure out how to do so without creating a circular reference. The data set pre-change works without issue as follows:

Circular referenceless.PNG

What I want to do is add some additional reporting off the existing schema which is able to get measures from extracted job data (workflowcode-procindex) as well as by fund database (database_code). New tables are in the red boxes:

Circular reference.PNG

This worked fine when putting my SLA dashboard but then I realized it completely breaks job source and failure config.

I can't wrap my head around how to fix this as the SLA data is meaningless without both workflow and Database information to cut it against.

Any ideas?

Cheers

Thomas

 

Labels (1)
1 Solution

Accepted Solutions
thomasmercer
New Contributor III

Re: Circular reference through four tables

Thanks for the advice. In the end it was all a bit much so I've decided to instead store SLA Config at a fundid level (rather than database code level) and link in directly to extracted job data. This turns the data model back into a nice snowflake:

Snowflake.PNG

Cheers

 

4 Replies
Highlighted
miskinmaz
Contributor II

Re: Circular reference through four tables

Hi,
You can do one of the following:
1. You can remove the unnecessary fields which are a part of a circular reference.
2. You can create a composite key to link tables.
3. Follow link table schema.
thomasmercer
New Contributor III

Re: Circular reference through four tables

Thanks for your response Miskinmaz but I still haven't been able to figure this out.


1. You can remove the unnecessary fields which are a part of a circular reference.

I feel like all the fields I'm using are necessary and required to be linked but happy to be challenged.

  • Workflowcode needs to be used to identify the type of job for analysis
  • Fundid is a subset of the database code (multiple fundid to one database code but not the other way around).

2. You can create a composite key to link tables.

I want to measure/record in SLA table at database level but my 'main' output (Extracted job data) is at fundid level. I'm not sure how/where to create a composite key with Extracted job data and SLA config having different fields


3. Follow link table schema.

I've done a bit of reading on link table schema but each time I try to do so I still end up with a circular reference

 

More thoughts and ideas are much appreciated! Cheers

Partner
Partner

Re: Circular reference through four tables

You need to make datamodell changes in some what, you need to decide where the to Job Config is to be done. When decided you will need to either remove the OR rename the workflowcode-procindex field where you want to break the connection, I assume in the new SLA table.

If you still are interested in gettinring the job config information to the SLA data you could add the Job Config table again with slightly current field names where the renamed workflowcode-procindex is identical to the field name in SLA table. 

Edit 06:07: added the last paragraph to my answer.

Please ekskuse my Norglish and Swenglish typos.
thomasmercer
New Contributor III

Re: Circular reference through four tables

Thanks for the advice. In the end it was all a bit much so I've decided to instead store SLA Config at a fundid level (rather than database code level) and link in directly to extracted job data. This turns the data model back into a nice snowflake:

Snowflake.PNG

Cheers