topic Re: circular references in QlikView

circular references

Mon, 10 Nov 2014 09:47:54 GMT

what is circular references and how we can remove.?

Re: circular references

senpradip007 — Mon, 10 Nov 2014 09:49:23 GMT

Go through this link http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

Re: circular references

sujeetsingh — Mon, 10 Nov 2014 09:55:44 GMT

When designing a datamodel in Qlikview, circular reference is quite a common thing.

There are several solutions to prevent such loops like the concatenate function or link table. Although the best solution to fix circular reference depends on the situation, I personally prefer the link table most of the times. When you have two fact tables for example, which shares more then one dimension, it means there is circular reference.

To prevent this situation we could use the concatenate function to create one major facttable. You could consider this solution when both tables have many similarities. When using the concatenate function, always dwell on the fact that, if the measure fields in both facttables are the same, calculations will cover all records of the table. Of course there is the possibility to create a field and specify the facttype so you can select ‘Fact1’ or ‘Fact2’ and use set analysis in your expressions but I think it’s clear that this isn’t an easy solution when the records always need to be separated in all expressions.

Example 1: Concatenate two fact tables.

//**************************************************************************
// FACT TABLE
//**************************************************************************
FACT:
load
‘Fact1’        AS Facttype
,    ID            AS %KEY_FACT1_ID
,    Measure
From……

CONCATENATE

load
‘Fact2’        AS Facttype
,    ID            AS %KEY_FACT2_ID
,    Measure
From……

What about concatenate if the fields in the facttables are quite different? Rationally this does not seem to be a good idea is it? Let’s demonstrate this with an example of two different processes. The facts are:

- The purchase of products
- The selling of products

When loading the original datamodel, Qlikview has to create a synthetic key because the dimensions date and product are shared. Although the synthetic key in this situation works fine, it’s better to prevent this.

When we concatenate the purchase and selling table, the synthetic key is gone and the problem is solved. Allthough naturally this just doesn’t feel like the ‘right’ way, there is no difference for the end user because the interface of Qlikview works on attribute level. For Qlikview, this is best practice and a good way of solving circular reference. Keep in mind that, for solutions with many tables, the overall picture for the developer is getting less clear. In that case it’s better (in my opinion) to use link tables.

Re: circular references

its_anandrjs — Mon, 10 Nov 2014 10:00:16 GMT

Hi,

In the load script use

1. Qualify or Unqualify

2. If possible join the tables

3. By renaming using alias names to the field

Regards

Anand

Re: circular references

Mon, 10 Nov 2014 10:09:30 GMT

among 2 fact tables sharing multiple dimension then we will get circular refernce.

Ways to remove

1)commenting field

2)renaming field

3)using qualifier unqulifier statments ,

Re: circular references

its_anandrjs — Mon, 10 Nov 2014 10:10:34 GMT

Hi,

Let assume this simple example

LOAD * Inline

[ Name,Address

A,India ];

LOAD * Inline

[ Name,Sale

A,150000 ];

Sale_Address:

LOAD * Inline

[ Sale,Address

150000,India ];

LOAD

Sale,Address as PersonAddress

Resident Salary_Address;

DROP Table Salary_Address;

Here you can join the two tables or qualify the tables

Regards

Anand

Re: circular references

Mon, 10 Nov 2014 10:15:51 GMT

Hi,

assume that if u have A,B,C table.you have relationship between A-B,B-C,C-A like this situation shows there is a circuler refference

to avoid this situation

1)Using qualify and unqualify statements

2)renaming field

3) Here we got Circular refference between A,B,C so we use below statement after all load statements of table

Loosen tables A,B,C;

Re: circular references

its_anandrjs — Mon, 10 Nov 2014 11:51:45 GMT

Hi,

Please close the thread if you got correct answer from the thread as appropriate answer correct or helpful.

Regards

Anand

Re: circular references

Wed, 26 Nov 2014 22:44:36 GMT

I've created a tutorial which includes a section on circular references.

The start of the tutorial is here: QlikView 11 Developer Tutorial

The circular references piece is here: QlikView 11 Developer Tutorial - part 18

Mike