topic Re: Help with circular reference in data model in QlikView

Help with circular reference in data model

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Help with circular reference in data model

jmvilaplanap — Tue, 18 Jul 2017 16:01:29 GMT

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

Re: Help with circular reference in data model

Tue, 18 Jul 2017 21:04:19 GMT

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?

Re: Help with circular reference in data model

manas_bn — Tue, 18 Jul 2017 21:52:31 GMT

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;

Re: Help with circular reference in data model

jmvilaplanap — Wed, 19 Jul 2017 08:25:35 GMT

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/autonumber.htm)

Regards

Re: Help with circular reference in data model

Thu, 20 Jul 2017 20:01:38 GMT

Thanks!

Re: Help with circular reference in data model

Thu, 20 Jul 2017 20:01:48 GMT

Thanks!