Understanding circular reference and joins

[iluilyas]

Hi All

I have difficulty in understanding circular reference (loops) in Qlik Sense (Qlik Sense Desktop 3.2 SR5)

I have below tables that logically connects as below:

1. Suppliers --> Purchase Orders (PO Header) --> PO Line Items (PO Details)

2. Suppliers --> Contract Lines (Header & Detail in one view)

3. Materials --> PO Line Items (PO Details)

4. Materials --> Contract Lines (Header & Detail in one view)

I'm loading below

Master	Transaction - 1	Transaction-2
Suppliers.SUPL_CD	PurchaseOrders.SUPLR_CD	Contract_Lines.SUPLR_CD
Materials.MATRL_CD	PO_Lines.MATRL_CD	Contract_Lines.MATRL_CD

No issues with SUPLR_CD.

But MATRL_CD is giving circular reference warning.

If i disable/give unique name to MATRL_CD in "Contract_Lines" table, its not throwing the warning.

I tried to split contract lines to two different tables by moving SUPLR_CD into CONTRACTS and MATRL_CD into CONTRACT_LINES but with no success.

If I'm not specifying MATRL_CD in CONTRACT_LINES and renaming it, i dont understand how the contract lines would be picked for the selected material.

Your help is much appreciated.

Regards

Ilyas

[kaushiknsolanki]

Hi,

Check below links for better understanding.

Circular References

Understanding and solving circular references ‒ Qlik Sense

Regards,

Kaushik Solanki

[petter · Accepted solution]

These are data models you can create to solve you issues:

[petter]

This might be helpful reading/study material: Get started with developing qlik datamodels

Most if not all is still valid in Qlik Sense as long it is about data modeling and load scripts.

[iluilyas]

Thanks petter-s‌ for your time and to hand-draw the model for me!

Model-C looks promising to me and i think i'm still trying to apply my original table structure which i assume i shouldnt be.

Let me try that. But, tell me one thing, What should I put in "Common Dimensions" if i wish to apply Model-B? I hope you are aware that Supplier and Material Masters are no where connected.

Rg, Ilyas

[petter]

Due to the associate model of Qlik you can easily see which Suppliers that have contracts and orders on which materials. So they will be indirectly connected whether you like it or not. It doesn't pose a problem - only open new opportunities for insights.

In data model B you would have to create matching keys between ORDER (a unique orderline key) and the CommonDimensions  table.

So in Order (order headers and order lines) it would be something like this:

%OrderLine   which could be made with a RowNo() function calculated at load time.

In CommonDimensions it would be the same keyvalues from %Orderline and in the same table you would need to have the matching %Supplier key and also %Material. When it comes to Contract it might be a %Contract that would be on the same row level as the previously mentioned keys OR it could be separate rows. In the latter case you wouldn't automatically be able to match between Suppliers, Orders, Materials AND contracts. Then you would solve that with the right collection of dimensions and or set expressions in your visualization.

[iluilyas]

Wonderful and thanks!

Ilyas

[iluilyas]

I chose to go ahead with Model-B and tried below:

PurchaseOrders: (Header & Detail lines)

(PURCHS_ORDR_NO & '-' & PURCHS_ORDR_DETL_NO & '-' & SUPLR_CD & '-' & MATRL_CD) AS PO_CONT_COMP_KEY

Contracts: (Header & Detail lines)

(CONTRCT_NO & '-' & CONTRCT_DETL_NO & '-' & SUPLR_CD & '-' & MATRL_CD) AS PO_CONT_COMP_KEY

PO_Cont_Comp_Key:

LOAD

(PURCHS_ORDR_NO & '-' & PURCHS_ORDR_DETL_NO & '-' & SUPLR_CD & '-' & MATRL_CD) AS PO_CONT_COMP_KEY

FROM [$(Master)\PurchaseOrders.QVD]

(qvd);   

PO_Cont_Comp_Key:

LOAD

(CONTRCT_NO & '-' & CONTRCT_DETL_NO & '-' & SUPLR_CD & '-' & MATRL_CD) AS PO_CONT_COMP_KEY

FROM [$(Master)\Contract_Lines.QVD]

(qvd);   

After this, in a table object, i tried with SUPLR_CD as dimension and the COUNT(PURCHS_ORDR_DETL_NO) as measure which showed the number of lines for each PO.

And in supplier name (SUPLR_NAME - from Supplier Master) filter, i selected a supplier.

But upon selection, the PO table object wasn't filtered with the POs of chosen supplier.

What have i done here?

Rg, Ilyas

[iluilyas]

I modified as below:

I chose to go ahead with Model-B and tried below:

PurchaseOrders: (Header & Detail lines)

(PURCHS_ORDR_NO & '-' & PURCHS_ORDR_DETL_NO) AS PO_CONT_COMP_KEY

Contracts: (Header & Detail lines)

(CONTRCT_NO & '-' & CONTRCT_DETL_NO) AS PO_CONT_COMP_KEY

PO_Cont_Comp_Key:

LOAD

(PURCHS_ORDR_NO & '-' & PURCHS_ORDR_DETL_NO) AS PO_CONT_COMP_KEY,

MATRL_CD,  SUPLR_CD

FROM [$(Master)\PurchaseOrders.QVD]

(qvd);  

PO_Cont_Comp_Key:

LOAD

(CONTRCT_NO & '-' & CONTRCT_DETL_NO) AS PO_CONT_COMP_KEY,

MATRL_CD,  SUPLR_CD

FROM [$(Master)\Contract_Lines.QVD]

(qvd);  

Then it worked.

I guess the previous one wasnt working because i missed MATRL_CD and SUPLR_CD in the common dimension.

Rg, Ilyas

[petter]