How circular reference can be avoided?

[#1]

Hi All

How circular reference can be avoided?

Regards,

Attitude

[#2]

Hi All

Can some please explain me their experience on this?

Thanks and Regards,

Attitude

[Miguel_Angel_Baeyens]

Hi,

As it happens in other software like MS Access or Excel, a circular reference (also called loop) happens when one table is reachable by two or more ways. As in QlikView tables are linked when two or more fields are named alike, the likeliest reason is that there are more than two tables with the same field names that link to each other.

Customers:       Orders:          Calendar:
CustomerID -->   CustomerID   
Date             OrderDate -->     OrderDate AS Date 

Customers links to Orders that links to Calendar that in turn links to Customers.

The usual way to avoid this is renaming fields, and sometimes, removing duplicated data from the data model.

Hope that helps.

Miguel

[hic]

By naming/renaming fields until you get the data model that you want. If you have circular reference, then you most likely have linked two fields that should not be linked.

There are many cases when a field semms to be the same as one from a different table, so you think they should be linked. But they still should not. For example, in a sales database you often have two different UnitPrice: One which is the suggested UnitPrice (in the Product table) and one which is the actual UnitPrice (in the OrderDetails table). These should not be linked, so you need to rename one of them.

[jagan]

Hi,

Circular reference can be avoided in the following ways

- You can rename a column.

- Use Qualify

- Concatenating Several Tables into One

- Loosely coupled tables

You can refer qlikview reference manual for examples.

Regards,
jagan.

[#6]

Hi

Circular references can be avoided by renaming any one of the field having same name in any one of the table, or by using qualify command on any one of the table. if u use qualify command then it ll rename all the fields in that particular table. so depend on ur data use alias or qualify and u can concatenate.

Regards

Arun Prasadh.N.

[#7]

Hi,

I recommend you to read tuh blog by Stephen Redmond About resolve Circular issues,

It's help me to understand more clearly about the Key Table Solution,

http://qliktips.blogspot.com/2011/05/rules-for-creating-keylink-table-in.html

Hope it help,

Yigal.

[#8]

Tell you what - the issue of reusing dimension does not have good enough answer from the product - in other words - QlikView doesn't let you to define (like other BI products let) a dimension (e/g the date dimension) and reuse it as many times as you like.

The example of the Date dimension (posted above by Miguel Angel Baeyens) is good example since we all know that the Date dimension may include many attributes' such as year, week, quarter, month and many others.

So when you have Customer Order, joint with 'Quotation", and 'Invoice' you may end up with 8 different dates and will have to load the Date dimension 8 times, and include objects for selecting different attributes of each date - e.g - give me all the customer orders that were shipped on Sundays during January 2012, invoiced on February, with Due date of payment for May and were a response for  a quotation from the last quarter of 2011.

As far as i see it, Currently, QlikView makes our life difficult in this aspect, hope to have new versions ready with better support for this in the coming years.

[#9]

I don't see how this is a problem. I work for a customs broker, and we have a ton of different date dimensions: creation, entry, invoice, release, liquidation, estimated arrival, exchange, etc. We simply adopted a naming convention: ReleaseDate, ShipDate, LiqDate, etc. This makes it very easy to find, for example, all shipments with "ReleaseDate > today()-45 AND LiqDate = NULL".

[Anonymous]

Circular reference can be avaoided by

1.renaming of the columns.

2. Having all the required columns only in the main fact table.

3. Create Link IDs.