Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist
Specialist

Re: Help with circular reference in data model

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

Highlighted
Not applicable

Re: Help with circular reference in data model

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?

Highlighted
Creator
Creator

Re: Help with circular reference in data model

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;

Highlighted
Specialist
Specialist

Re: Help with circular reference in data model

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

Highlighted
Not applicable

Re: Help with circular reference in data model

Thanks!

Highlighted
Not applicable

Re: Help with circular reference in data model

Thanks!