1 Reply Latest reply: Sep 24, 2015 3:46 PM by Jonas Melo RSS

    Help on Linking Data

      Hello I have data in the following way.

       

      Target Table1:

       

      Field1 Field2 Field3 Field4  Target    Year

      1           2        3         4         100$    2015

      1           33       44      55      200$     2015

      1           2         3          5       300$   2015

      A key(Target1Key) is formed with the combination of Field1&Field2&Field3&Field4 and is joined to Main Table


      Target Table2:

      Field1 Field2 Field3 Target Year

      1          2        3         20$  2015

      2          3         4        30$  2015

      A key(Target2Key) is formed with the combination of Field1&Field2&Field3 and is joined to Main Table


      Main Table:


      Field1 Field2 Field3 Field4 Savings Field5 Field6 Year

      1           2        3       4         100$     AB     CC   2015

      1           2        3       4         100$     BB     CC   2015

      1          33      44       55      200$      BB     CD   2015


      A key is formed with the combination of Field1&Field2&Field3(Target2Key) and Field1&Field2&Field3&Field4(Target1Key) .

      When I want to show savings Vs Target for 2015


      The Sum(Savings) is 400$ and Target1 is Sum(Target1) is = 300$(100 + 200) instead of 600$(100+200+300).

      Field1 Field2 Field3 Field4

      1           2         3          5     This combination is not present in Main Table and Since Main table is the driving table. So eliminating the 300$ because of non association.


      How to over come this problem? I have to take 300$ also into consideration. I cant join or concatenate with my Main table as I have other fields in Main table(When they are selected it will be a problem). Please suggest.


      BR,

      Avinash