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: 
Not applicable

Help with circular reference in data model

Hi all, I have a circular reference, but I'm not sure how to get rid of it while keeping the data in the structure that I need.

circle.png

Let me explain:

ItemDim is a list of items and item codes, and the sales data for those items is in SalesFact.

ItemCategories is an excel doc that links item numbers to a Product, like item 5688 is under the Mobile App Product.

The $Syn 3 table was created because of my circular reference.

Cases and Projects are a list of cases/projects, that are also tied to a product, but this tie-in is not through the previous excel doc, but a different hardcoded method (if case# = 2221, then Mobile App).

In my document, I need a chart that shows values for cases and products, and puts it against data from item revenues. The issue is, in order to have these on one chart with the correct values, I need to combine the Product List from Items and the Product List from Cases/Projects.

I created a TestFinalProduct table, and called it the same thing in both sections, but it gave me a circular loop.

Any ideas?

6 Replies
jmvilaplanap
Specialist
Specialist

Hi,

Normally to check the synthetic links, I use the other view (this view is a little messy for me).

Anyway, the circular reference and the synthetic key are different problems, you can "live" with a synthetic but not with a circular.

I saw that the circular reference is because "item_key", could you take off this field? Or because the "synth1".

Try to fix this problem first, will be easier to fix the synthetic (normally just concatenating fields)

Regards

Not applicable
Author

Should I join or concatenate?

Item Dim is a list of items, and SalesFact is the sales orders for those items.

Would it help if I joined/concatenated those two tables?

manas_bn
Creator
Creator

I would consider creating a Link Table which has all the key fields. The data model will look cleaner and you won't have any synthetic keys OR Circular references to worry about.


Ex:

Cases:

Load

Field1, Field2,...//All fields EXCEPT key fields,

ProdID&Date&Month&Prod&Cust*Ctgy as %CasesKey

from Cases.qvd;

Projects:

Load

//All fields EXCEPT key fields,

ProdID&Date&Month&Prod&Cust*Ctgy as %ProjectKey

from Projects.qvd;


Sales Fact:

Load

//All fields EXCEPT key fields,

ProdID&Date&Month as %SalesKey

from Sales.qvd;


Link Table:

//Load all Key fields and Key Columns from all fact tables

Load ProdID&Date&Month&Prod&Cust*Ctgy as %CasesKey, //All Key fields from Cases.qvd;

Load ProdID&Date&Month&Prod&Cust*Ctgy as %ProjectKey,//All Key fields from Projects.qvd;

Load ProdID&Date&Month as %SalesKey,//All Key fields from Sales.qvd;

jmvilaplanap
Specialist
Specialist

Hi,

In my opinion, the best is to join ItemDim and ItemCategories because are strong relationated and makes sense logically. With this you can say bye to the circular reference.

For the synt, try to concatenate the fields and rename them.

For better performance, use Autonumber function for the keys (http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/CounterFunctions/autonu...)

Regards

Not applicable
Author

Thanks!

Not applicable
Author

Thanks!