topic Circular Reference? in QlikView

Circular Reference?

Wed, 17 Aug 2011 21:35:03 GMT

Is it okay to have circular reference in this case? I've three dimensions and one fact table. Client Dimension: It can have multiple instance of Client_GID but unique within a SalesForce_ID. Similarly, SalesForce dimension can have multiple instance of same zipcode, but unique within a SalesForce. In the front end I am planning to have always one SalesForce_ID selected. Is it going to create a problems in Fact report?

Re: Circular Reference?

Oleg_Troyansky — Wed, 17 Aug 2011 22:26:26 GMT

No, it's not OK. QlikView won't allow any circular references, no matter what...

In your case you should give up on one of the links. I'd consider dropping Zip Code from the Sales Force table, since you already have Zio Code in the Fact Table. This will resolve the "loop".

cheers,

Oleg

Circular Reference?

Thu, 18 Aug 2011 22:56:13 GMT

If I drop Zipcode from the SalesForce table and I select District = 'Dist Wilkes Barre, PA' in SalesFiorce_ID = Diabetes, how would it work? Since this district is made up of 50 zips how would it get to the Fact table to report only those 50 zips?

Re: Circular Reference?

Oleg_Troyansky — Fri, 19 Aug 2011 01:47:44 GMT

Based on your diagram, Sales Force Data is associated with Clients based on SalesForce ID, and Clients are linked to the Fact table. When you select a District, it will select all associated Clients and therefore all the associated Zip codes. I don't see a problem there.

Circular Reference?

Fri, 19 Aug 2011 20:51:36 GMT

If I select a District in Salesforce(After removing Zip from SF) It will select all associated clients for a Particular SalesForce and not clients within a District of a SalesForce. So, as soon as I remove ZipCode from SF, and select a District within a SalesForce, my Client table will Give me list of all clients present in a SalesForce and not within a District of that particular SF. Isn't that correct? I guess the best way for me is to try your suggestion and then let you know what happens. Thanks!

Circular Reference?

Fri, 19 Aug 2011 23:23:47 GMT

Dropping a Zipcode field from SalesForce table doesnt work. As I said if a User picks a District; instead of gettting only Zipcodes specific to only that District, QV returns all Zipcodes from entire SalesForce to which that District belongs. Any other Solution to avoid Circular reference?

Re: Circular Reference?

swuehl — Fri, 19 Aug 2011 23:44:05 GMT

You could probably join the geography table with the salesforce table. Then rename %zipcode, city and state of the joined table to e.g. SalesforceCity, SalesforceZipCode and SalesforceState.

edit: I mean, join one copy of the geography table. Another geography table could still be linked to the fact table as is.

edited by swuehl

Re: Circular Reference?

swuehl — Fri, 19 Aug 2011 23:53:07 GMT

Or without joining, have two copies of the geography table, one for the fact table, one for the salesforce table with different fieldnames.

Both soultions should work, if the descriptive data for salesforce and client (geographical descriptions) could be viewed as separate data, e.g. like Names (Salesforce Name and Client Name for example) and won't be selected in the same place (like you probably select Client Name and Sales Name separately, same for Client Zip and Salesforce Zip).