Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How circular reference can be avoided?

Hi All

How circular reference can be avoided?

Regards,

Attitude

Tags (2)
12 Replies
Not applicable

How circular reference can be avoided?

Hi All

Can some please explain me their experience on this?

Thanks and Regards,

Attitude

Re: How circular reference can be avoided?

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

How circular reference can be avoided?

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.

MVP & Luminary
MVP & Luminary

How circular reference can be avoided?

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.

Not applicable

How circular reference can be avoided?

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.

Not applicable

How circular reference can be avoided?

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.

Not applicable

Re: How circular reference can be avoided?

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.

Not applicable

Re: How circular reference can be avoided?

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".

vinay_bangari
Valued Contributor III

Re: How circular reference can be avoided?

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.