topic New to QlikView - join problem in QlikView

New to QlikView - join problem

Tue, 26 Jul 2011 10:02:26 GMT

I am currently evaluating QlikView.

I have built a fairly simple demo application that extracts data from a 3rd party time management system and presents information about time spent on projects and supporting applications. I would like to show which projects have been undertaken for each application. Unfortunately I am working with a 3rd party database that does not provide a link between projects and applications.

For the purpose of the demo application I thought I would create an Excel worksheet that linked each project to an application. However when I load this into QlikView I receive a warning about looping and advice that one of the tables will be loosely coupled which may cause unpredictable results.

I do not understand why this is happening as the additional data is just a simple link table between 2 other tables in the schema.

Any ideas how I can get round this?

New to QlikView - join problem

stigchel — Tue, 26 Jul 2011 12:58:30 GMT

Without any information on which tables and fields there are in your application, it is difficult to answer your question. Fields with the same name are automatically linked by Qlikview, probably there is one undesired link. Looping occurs when a Field is linked to another table in which another field is linked to a third table, which again is linked to the first table, in other words a loop. Check your table viewer, from the menu File\Table viewer or by pressing Ctrl T, for the links qlikview made. The loosely coupled fields/tables are marked (From my memory, dotted lines). Remove any undesired links by using the 'qualify' statement in the load script e.g. qualify Field2; Field2 will not be linked.

Re: New to QlikView - join problem

Tue, 26 Jul 2011 13:28:59 GMT

Thanks for your reply.

Basically there is a Projects table that contains Project Id and Project Name and and Applications table that contains Application Id and Application Name. Each of these is linked to a table that has detail of time spent on the project or application. Links are by the Project Id and Application Id fields, much as you would expect.

I am trying to link the Applications and Project table together with a new table called ApplicationProjects that just contains Application Id and Project Id. This is what causes the loop.

Re: New to QlikView - join problem

stigchel — Tue, 26 Jul 2011 13:51:59 GMT

From what I understand from your fields you should noet get a loop unless Time Spent has the same name and is also linked. I did a small test, see what looks like your setup:

Re: New to QlikView - join problem

Tue, 26 Jul 2011 15:41:45 GMT

Here is a BEFORE and AFTER view of the entire table layout.

New to QlikView - join problem

stigchel — Thu, 28 Jul 2011 14:32:47 GMT

The loop is caused by the EmployeeID, Month Number and Month name fields which are in both your Time spent tables. If they are unrelated name them differently, otherwise join load them in AppId + ProjectID table and remove them from the time spent tables. (from the looks of it, not sure what the exact relation between these fields are)

Re: New to QlikView - join problem

Fri, 29 Jul 2011 13:24:57 GMT

Thanks for coming back again. I have tried to simplify the model in several different ways but as soon as I add the

link table the loop re-appears. Current model without link table is shown below and is simpler than original one I sent. Must admit that I don't see why adding the link table should cause such a problem? Appreciate the time you have spent on this though - thanks!

New to QlikView - join problem

SunilChauhan — Fri, 29 Jul 2011 13:30:34 GMT

if u have a model with created Circular loop. then u break on link which u feel is unneccessuary.then u have no more

circular loop in ur model

Re: New to QlikView - join problem

Fri, 29 Jul 2011 13:40:48 GMT

Now that you mention it, I can see in the Source Table View that there is an unnecessary link.

How do you break the link?

New to QlikView - join problem

SunilChauhan — Fri, 29 Jul 2011 13:44:04 GMT

rename the linked field in one table.

Sunil