12 Replies Latest reply: Apr 13, 2013 12:00 PM by Mirek Subrt RSS

    How circular reference can be avoided?

      Hi All

       

      How circular reference can be avoided?

       

      Regards,

      Attitude

        • 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?
              Miguel Angel Baeyens de Arce

              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

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

                • How circular reference can be avoided?
                  Henric Cronström

                  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 mohan rao appala

                  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?

                    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.

                    • Re: How circular reference can be avoided?

                      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