Multiple table joins

[#1]

Hi Guys

Could someone please help me with this scenario:

Say I have 3 tables:

Project:

Project ID

Project Name

Year

Cost

Cost ID

Cost Name

Year

Sales

Sales ID

Sales Name

Year

When I join all 3 by Year, I get an error below.

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.

Just need to join them for criteria on the QVW.

Please help

[#2]

Research creating a Master Calendar. This will allow you to select a year from the master calendar and see all available records.

[richard_chilvers]

Remember QlikView by default will always link fields with the same name. In your case, Year.

You may find it useful to read the section in the manual on Avoiding Circular References.

Good luck.

[#4]

Hi

From the scenario explained, I dont see any chance of forming a Loop. There could be a possibility that, other tables used in script are causing problem.

Please upload a pic of table structure after reload, it will be easy to identify the issue.

Krishna

[#5]

There are quite a lot of posts on that. do you have anyone that has worked for you? or you know would work?

Note that these "Year" are in different QVDs. I am hoping once I can fix the year, same logic can be applied for month.

Thanks

[#6]

Hi

This is what the structure looks like.. its the "Year" that is the problem .. I still have more QVDs to add with Year

[richard_chilvers]

Hi

The attachment highlights the problem, where QlikView creates links based on field names and therefore makes a 'loop'.

It may be that the values in your data mean there is never any ambiguity but QlikView cannot know that. So you need  to decide how to manage the similar data fields. Either let QlikView do the best it can, or take control yourself.

[#8]

Hi Maibangbee

looking at the attached image, I would say that its the inclusion of "Year" field in the folowing tables that is causing the issue:

"COVP"

"Sales Info"

Potentially, you should remove you link here (by renaming "Year" to something similar but unique (ie SalesYear).

By doing this, you can still link your various documents by the good fields (ie "WBS Object NUmber").

This should overcome your looping error message, where multiple tables are linked by multiple fields (see the "COVP", "Sales Info" & "WBS3" triangle)

Hope this helps?

Kind regards,

Rich

[richard_chilvers]

Rich is following my line of reasoning too (must be in the name)

Giving Year an ALIAS (ie renaming it) would be one solution. But I can see it may not give an ideal solution when analysing the data when it may be necessary to select on year. It will also be an issue when further QVDs are introduced and when the Month field is introduced.

Regards

[#10]

OK - We are now on the same thinking too -:) I asked exactly the same Question. Hmmm.. drawing board again