topic Circular Reference - Loops in QlikView

Circular Reference - Loops

Sat, 17 May 2014 15:43:57 GMT

I have a loop in my data model and is caused by 2 Fact tables having different granularities on 2 hierarchical dimension.

Some background:

I have 2 facts, "Market Equity by Product" and "Market Value by Portfolio"
both facts have reference to [Report Date]
[Market Equity by Product] references [Product]
[Market Value by Portfolio] references [Portfolio]
Portfolio rolls-up to a Product (1 product - many portfolios), I think this is causing the LOOP as 1 fact has Portfolio granularity while the other fact has Product granularity

How do I remove the LOOP while still being able to filter on [Product] on my 2 facts?

Thanks in Advance for any help...

Re: Circular Reference - Loops

Anonymous — Sat, 17 May 2014 15:58:36 GMT

This looks like something desperately in need of the applymap() function.

Have a look at this blog post that explains it.:

Don't join - use Applymap instead

Re: Circular Reference - Loops

fkeuroglian — Sat, 17 May 2014 20:12:48 GMT

Hi, a link table could be an option

see this

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

Re: Circular Reference - Loops

hic — Sun, 18 May 2014 12:41:21 GMT

I looks as if a Portfolio always is associated with one Product only. If so, you should do the following:

Portfolio2Product:

Mapping Load %Portfolio_Id, %Product_Id From Portfolio;

FactTable:

Load *,

ApplyMap('Portfolio2Product',%Portfolio_Id) as %Product_Id

From [Market Value By Portfolio];

Concatenate

Load * From [Market Equity By Product];

Then add all dimensions, with the small change that the Portfolio table should not contain Product any more.

HIC

Re: Circular Reference - Loops

PrashantSangle — Sun, 18 May 2014 14:53:51 GMT

Hi,

Just rename '%portfolio_Id' your field from Portfolio table

Because your are getting Portfolio details via Product table.

So that you access Portfolio data.

Therefore there is no need to link Portfolio table to Market Value By Portfolio

Regards,

Re: Circular Reference - Loops

Mon, 19 May 2014 13:38:10 GMT

Thanks Fernando Keuroglian the link lead me to another link which helped me resolve the issue.

I used Generic keys to break the loop between portfolio and Product following the approach here Generic keys
Because of Generic keys, I was able to concatenate [Market Equity by Product] to [Market Value by Portfolio]. This seem to be a simpler solution than creating a Master Link Table to join my 2 Facts

Here's the final model:

Re: Circular Reference - Loops

Mon, 19 May 2014 13:48:25 GMT

Hi Henric Cronström,

It seems like your solution to put %Product_Id in the Fact would have been a very good one too. I actually followed the Generic keys approach on your earlier post which worked perfectly with my requirement.

I'll also keep this in mind as another approach. If I may ask, which of the 2 solutions would you normally use to solve Loop problems. Thanks again for the help you are providing this community.

Re: Circular Reference - Loops

hic — Mon, 19 May 2014 13:59:32 GMT

I almost always use a concatenated fact table and not a link table. The concatenated fact table has much better performance.

HIC