topic Re: Breaking Circular loop in QlikView

Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 17:16:45 GMT

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

Re: Breaking Circular loop

shree909 — Mon, 05 Nov 2012 17:44:29 GMT

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.

Re: Breaking Circular loop

its_anandrjs — Mon, 05 Nov 2012 17:58:54 GMT

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

Re: Breaking Circular loop

swuehl — Mon, 05 Nov 2012 18:00:33 GMT

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.

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 18:17:44 GMT

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?

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 18:19:01 GMT

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

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 18:20:56 GMT

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.

Re: Breaking Circular loop

MayilVahanan — Mon, 05 Nov 2012 18:28:22 GMT

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

Hope it helps

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 18:33:52 GMT

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.

Re: Breaking Circular loop

MayilVahanan — Mon, 05 Nov 2012 18:38:26 GMT

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

Re: Breaking Circular loop

its_anandrjs — Mon, 05 Nov 2012 18:41:55 GMT

Hi,

From my side you have to make two table as single table like

1. Calls and Leads or other one

2. Executive and Leads

by joining with its id

Regards,

Anand

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 18:42:23 GMT

Thanks,

But in the process of join u broke a link. I am not able to find the assigned executive to a lead.

Link from Leads to Executives is broken. this can be done without a join by just breaking a link.

Please help. I have attached the excel sheet as well.

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 18:45:41 GMT

Thanks Anand.

I tried doing that as well but the problem is I need Loop is not broken.

Can u see the other posts by Mayil. He has also mentioned the same but in the process a link is getting broken which I cant do.

I have shared the qvw and data excel file.

Pleasssse help

Re: Breaking Circular loop

MayilVahanan — Mon, 05 Nov 2012 18:49:02 GMT

I think attached file helps you..

Re: Breaking Circular loop

CELAMBARASAN — Mon, 05 Nov 2012 18:50:08 GMT

Hi,

You go with creating fact tables or link tables

for more information please read this article

http://www.quickqlearqool.nl/?p=910

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 19:02:24 GMT

Logically I still doubt honestly due to following

A executive is assigned to Lead

That executive my not be calling the lead

Calls table is linked to leads and executive

thus what data will the merged table hold(Say I merge Leads and Calls) ?

Which Executive id to hold? the assigned or the calling?

I think Duplicating Entire Tables seems the only answer.

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 19:04:08 GMT

Can u also let me what do u mean by to use Set Analysis once data is duplicated?

Re: Breaking Circular loop

MayilVahanan — Mon, 05 Nov 2012 19:22:26 GMT

ExecutiveId1 has value 1,2,3 in executive table.

ExecutiveId1 has 1,2 in Calls table

ExecutiveId has 1,2 in Lead table.

Qv automatically join by names, so ExecutiveId1 join executive table n calls table.

LeadId1 join the Lead n Calls table because of same name in both of it..

So it avoid the synthetic key as well as loop structure by renaming the field.

Hope you can understand it..

Re: Breaking Circular loop

CELAMBARASAN — Mon, 05 Nov 2012 19:57:55 GMT

Hi Mayil,

What happens there is no call made between the one particular lead and executive?

Hi Saurabh Pandit

Is there a chance of no call between lead and executive?

Re: Breaking Circular loop

Anonymous — Mon, 05 Nov 2012 20:01:29 GMT

Yes. That is possible. I am also using Table box and trying to find a solution.