Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

saurabh_pandit
Not applicable

Breaking Circular loop

Hi,

I am new to Qlikview.

I learnt that Qlikview uses Associative property so we cannot keep circular relationship like SQL.

In SQL even if there is circular relationship then we can use joins in our query and get required report.

Please let me know how to do it in Qlikview

I am having three tables

Leads

Calls

Executives

All three are related and I cannot break even a single link due to following reasons:

A Lead is assigned to Executives

Calls are made to lead and calls table has lead id and executive id to tell who made the call.

Executives Table has leadid and call id.

Problem being that a executive may call a lead that he is not been assigned as well. So I cannot break any of the link. (as many may think of breaking the Executives and Calls link)

I will share the qvw if that would help.

Thanks

1 Solution

Accepted Solutions
saurabh_pandit
Not applicable

Re: Breaking Circular loop

I think the solution is to duplicate tables.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references

Thanks HIC for the solution.

20 Replies
shree909
Not applicable

Re: Breaking Circular loop

hi,

U get circular loops when u have the same field names from the  2 differnt tables .

either u remane the first table or second table depending on ur requirement.

try remanimg the fields.

its_anandrjs
Not applicable

Re: Breaking Circular loop

Hi,

If possible try to rename the field which are same name or either rename it if necessary or if not necesary so drop that field from one of the table.

Regards,

Anand

swuehl
Not applicable

Re: Breaking Circular loop

You might want to try duplicating for example the Leads table, renaming the fields and having on table linked to Calls (CalledLeads) and one to Executives (AssignedLeads).

You can then use set analysis to analyze for example Leads that are assigned to an Executive but have never been called.

saurabh_pandit
Not applicable

Re: Breaking Circular loop

The problem of Synthetic keys can be solved by renaming, but here I have a loop.

Please find attached the qvw and diagram.

When taking mouse over the linking key I am getting some information about Information Density 100% and Subset ratio 67% what is that?

saurabh_pandit
Not applicable

Re: Breaking Circular loop

I cannot drop any field thats the problem. Is duplicating the data the only solution as I need to duplicate the complete Master /Fact table

saurabh_pandit
Not applicable

Re: Breaking Circular loop

Thanks for understanding my problem but duplicating the data the only solution?

I will be making not a table but the whole data structure duplicate as related tables will also be required apart from the Leads table thats the Fact Table.

I am attaching the qvw. Please let me know.

mayilvahanan
Not applicable

Re: Breaking Circular loop

Hi

To avoid the loop. you can join any one of the table with another. By doing this, loop can be removed..

Hope it helps

saurabh_pandit
Not applicable

Re: Breaking Circular loop

Thanks Mayil,

If I do a join then I think a problem of synthetic keys is created between the new tables else again a loop between two tables is created as we cannot link two tables with two fields. There has to be a single linking field. Correct me if wrong.

Can u help me with the code to join and which tables do u think? I have attached the qvw.

mayilvahanan
Not applicable

Re: Breaking Circular loop

HI

Try like this

Leads:

LOAD LeadId as [%LeadId],

     ExecutiveId as [%Leads_Executive ExecutiveId],

     LeadDetails

FROM

[..\Desktop\Circular Loop.xlsx]

(ooxml, embedded labels);Directory;

Join(Leads)

Calls:

LOAD CallDetails,

     LeadId as [%LeadId],

           ExecutiveId as [%Calls_Executive ExecutiveId]

FROM

[..\Desktop\Circular Loop.xlsx]

(ooxml, embedded labels, table is Calls);

Executive:

LOAD

           ExecutiveId as [%Calls_Executive ExecutiveId],

     ExecutiveDetails

FROM

[..\Desktop\Circular Loop.xlsx]

(ooxml, embedded labels, table is Executives);

I can't able to reload it because of xls file..

so hope it helps