Circular Reference - Loops

[#1]

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...

[Anonymous]

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

[fkeuroglian]

Hi, a link table could be an option

see this

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

[hic]

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

[PrashantSangle]

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,

[#6 · Accepted solution]

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:

[#7]

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.

[hic]

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

HIC