topic Re: Removing Circular Reference in QlikView

Removing Circular Reference

Tue, 14 Jul 2015 05:02:07 GMT

Hi Team,

I have a confusion in resolving Circular Reference from my Application.

I want the most easiest way to resolve it.

I am giving an example of my Table structure:

Table1: Table2: Table3

Team, City Country

Country Team City

How will i remove circular reference from it. Since, All the columns are important in my application I cannot omit it

Regards,

Priyanka

Re: Removing Circular Reference

Tue, 14 Jul 2015 05:04:36 GMT

You can rename the Country column in any one of the table.

Re: Removing Circular Reference

maxgro — Tue, 14 Jul 2015 05:06:10 GMT

this can help

Circular References

Re: Removing Circular Reference

maksim_senin — Tue, 14 Jul 2015 05:10:47 GMT

Hi,

Decision on what to do with data depends not only on the data themselves but also on the goals of the application, so first you should understand whether all of the duplicates play the same role or not. E.g. in one app I have two regions - on is plan region and other is fact region of sales.

Say if your Contry fields play the same role it's more then reasonable to put in into one table, as well as other duplicates. Here we come to the idea of link table but again that depends on your requirements.

Best regards,

Maxim

Re: Removing Circular Reference

manideep78 — Tue, 14 Jul 2015 05:15:15 GMT

Hi Priyanka018‌

You can rename the any one of the Country Columns or You can go for Loosely coupled tables concept.

Please look for loosely coupled tables concept.

Re: Loosely Couple Table

Circular References

Regards

Manideep

Re: Removing Circular Reference

Tue, 14 Jul 2015 05:22:17 GMT

Hi,

Try using LOSSEN TABLE tablename for one of the tables.

Re: Removing Circular Reference

Anonymous — Tue, 14 Jul 2015 05:22:27 GMT

Rename Country .

Re: Removing Circular Reference

Tue, 14 Jul 2015 05:27:59 GMT

Hi,

Reaname the fields i.e using alias

or use Qualify and Unqulify keyword

Example

Table1:

LOAD * INLINE [

    Team, Country

    A, India

    B, AUS

    C, PAK

];

Table2:

LOAD * INLINE [

    Team, City

    A, bangalore

   B, Sydeny

];

Table3:

Qualify *;

UNQUALIFY City;

LOAD * INLINE [

    Country, City

    India, banglore

    AUS, Sydeny

];
UNQUALIFY;

Re: Removing Circular Reference

yadav_anil782 — Tue, 14 Jul 2015 05:39:28 GMT

Priyanka plz go throw this ling for understanding circular loop-

Circular References

Re: Removing Circular Reference

jonathandienst — Tue, 14 Jul 2015 05:39:53 GMT

There are many ways of resolving the problem, but the real problem is that you need to understand your requirements and your data and decide what you need to do with the 'extra' Country field. Is it:

redundant,
a different value,
one of them can override the other
...

How you handle it is dependent on what it means and what your users will require. You cannot just load and pray.

Re: Removing Circular Reference

jagan — Tue, 14 Jul 2015 05:42:55 GMT

Hi,

You can join the tables instead of managing as three different tables, then you won't get this issue.

Regards,

Jagan.

Re: Removing Circular Reference

Tue, 14 Jul 2015 23:52:05 GMT

Hi Reshma,

Thank you for your instant replay

Can you please tell me which table should i consider, to include as Lossen table

Regards

Priyanka

Re: Removing Circular Reference

Wed, 15 Jul 2015 00:24:50 GMT

Hi Ranga,

Thank you for your reply with such a great example.

But, Qualify and Rename does some what the same thing. ie Qualify uses Tablename.Columnname & using Rename would directly change the column name. Which is not feasible in my case.

I have read in Qlikview 11 for developers book as well as few blogs about "Loosen Table" can you please help me out with this.

I am little confused on which table should i select for loosen table concept, and how do we identify it from this 3 tables

Thanks in Advance

Regards

Priyanka

Re: Removing Circular Reference

jagan — Wed, 15 Jul 2015 02:09:13 GMT

Hi,

Not sure why are going for complex method instead of simply solving this by joining any two tables?

Regards,

jagan.

Re: Removing Circular Reference

qlikviewwizard — Wed, 15 Jul 2015 02:44:57 GMT

It is good method.

Priyanka018‌

Why you have chosen that complex method? Is there any specific reason?

Re: Removing Circular Reference

qlikviewwizard — Wed, 15 Jul 2015 02:45:54 GMT

Hi Priyanka018

Can you give some sample data to provide quick solution.

Re: Removing Circular Reference

Thu, 16 Jul 2015 15:34:35 GMT

Hi,

Apology for delayed response.

Attached is the QVW file for your reference.

Please suggest any appropriate solution.

Regards

Priyanka

Re: Removing Circular Reference

Peter_Cammaert — Thu, 16 Jul 2015 15:48:19 GMT

Use this code to laod your Employees:

Employees:

LOAD EmployeeID,

LastName,

FirstName,

Title,

TitleOfCourtesy,

BirthDate,

HireDate,

Address,

City AS EmployeeCity,

Region,

PostalCode,

Country AS EmployeeCountry,

HomePhone,

Extension,

Photo,

Notes,

ReportsTo,

PhotoPath;

SQL SELECT *

FROM NORTHWND.dbo.Employees;

The reasoning is that it's probably not very useful to look at facts for which both the Employee (Sales Executive) and the Customer live in the same city.

Best,

Peter

Re: Removing Circular Reference

Fri, 17 Jul 2015 05:22:45 GMT

Hi Peter,

Thank you for your instant response.

Previously, I also thought of renaming this to columns to avoid reference, but when I checked in SQL 25% of the city is matching in both the tables.

What do you think, should I proceed with the same logic or we need to find some different solution.

Regards

Priyanka

Re: Removing Circular Reference

Peter_Cammaert — Fri, 17 Jul 2015 08:23:14 GMT

Well that is up to you and your document users: will they ever want to see the data for which both customers and employees reside in the same city? I guess not, so this simple Renaming trick will do. But then, I won't be a user of your QlikView document...

Best,

Peter