Help with circular reference in data model

[#1]

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.

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?

[jmvilaplanap]

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

[#3]

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]

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]

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

[#6]

Thanks!

[#7]

Thanks!