topic Re: How circular reference can be avoided? in QlikView

How circular reference can be avoided?

Tue, 17 Jan 2012 09:21:34 GMT

Hi All

How circular reference can be avoided?

Regards,

Attitude

How circular reference can be avoided?

Thu, 02 Feb 2012 13:10:03 GMT

Hi All

Can some please explain me their experience on this?

Thanks and Regards,

Attitude

Re: How circular reference can be avoided?

Miguel_Angel_Baeyens — Thu, 02 Feb 2012 13:22:54 GMT

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?

hic — Thu, 02 Feb 2012 13:23:27 GMT

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.

How circular reference can be avoided?

jagan — Fri, 03 Feb 2012 07:54:50 GMT

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.

How circular reference can be avoided?

Fri, 03 Feb 2012 08:36:06 GMT

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.

How circular reference can be avoided?

Fri, 03 Feb 2012 09:28:02 GMT

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.

Re: How circular reference can be avoided?

Thu, 23 Aug 2012 10:04:27 GMT

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.

Re: How circular reference can be avoided?

Fri, 21 Sep 2012 14:17:50 GMT

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

Re: How circular reference can be avoided?

Anonymous — Fri, 21 Sep 2012 17:25:58 GMT

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.

Re: How circular reference can be avoided?

Sun, 23 Sep 2012 07:34:47 GMT

kevin - yes it is true! but then you have to load the date dimension as many times as you have date keys in your facts - but never mind about it, i want to know what Link IDs are (mentioned by vinay).

Re: How circular reference can be avoided?

hic — Sun, 23 Sep 2012 13:08:34 GMT

I think you should load the date dimension as many times as you have date keys. A query where you demand a specific ShippingMonth is never the same as if you demand the same month as ReleaseMonth, and the best way to make this difference obvious to a user is to have two different fields for the two.

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

HIC

Re: How circular reference can be avoided?

Sat, 13 Apr 2013 15:58:22 GMT

Hi,

one of possible solving of circular reference problem is (as was mentioned here) to rename and to add some new columns in one or more tables.

Take this example: You have three tables (T1, T2 a T3) and all of them has column key X, which you WANT to use for JOIN of them, but not like the CIRCLE but like the LINE: T1--->T2--->T3. Your target is to access T3 table from T1 table not directly, but indirectly - through T2 table.

So you will change your original definition of tables from this definition

1) T1 has one X column and other columns which are not important for us just now;

2) T2 has one X column and other columns...;

3) T3 has one X column and other columns...

to this new definition:

1) T1 has one renamed X column into X1_2 column and other columns...;

2) T2 has one renamed X column into X1_2 column, one new X2_3 column (with the same value) and other columns...;

3) T3 has one renamed X column into X2_3 column and other columns...

Regards,

Mirek