Breaking Circular loop

[Anonymous]

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

[shree909]

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]

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]

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.

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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]

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

[Anonymous]

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]

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