0 Replies Latest reply: Jan 24, 2017 4:59 PM by Eugene Smit RSS

    Joining data outside central link table

    Eugene Smit

      Could use your help. I have a chart I'm trying to draw that uses three dimensions of data: we have portfolios, portfolios consist of securities (held at certain weights) and securities have various attributes. I'm trying to chart aggregated weighted attribute values at the portfolio level. ie. I want to take the security weights held in the portfolio, multiply those by some attribute value for the security and then sum them up.

       

      To give an example: Portfolio A consists of two securities, Apple and Google. Google's weight is 40% and Apple's weight is 60%. Google's attribute value is 1.1 and Apple's attribute value is 3. I need the following:

       

      ( 0.4 * 1.1 ) + ( 0.6 * 3 ), or SUM( weight * attribute value )

       

      The problem I think I have is that the central link table in the data model doesn't have entries for every single combination of portfolio ID, security ID and attribute ID, mostly because I think the link table would too big if I had to try. Instead I took an easier route and linked accounts to securities and then securities to attributes, so the link table looks something like the following:

       

       

      Portfolio IDSecurity IDAttribute ID
      11NULL
      12NULL
      13NULL
      NULL11
      NULL12

       

      However, when I try Sum( weight * attribute value ) it doesn't return any data. Both Sum( weight) and Sum( attribute value) work.

       

      So I think it's caused by the fact that there is no direct link between the portfolio and attribute. Both link to security, but to get from portfolio to attribute it would need to go through the link table twice.

       

      Is there a way to get this done without having the link table have a record for every combination of portfolio, security and attribute?