Forum Discussion

vijendras's avatar
vijendras
Frequent Visitor
8 years ago

Left Join in calculated table

I have 2 tables which are not joined together now as there is no unique values in either table to join them

 

Offer- C_No and Ref_No and other feilds

Offer_Discount - C_No and Ref_No and other feilds

 

I am trying to get the below result in report and stored it in a calculated table.  I bleive I can also add a new column 'Total_amount' in Offer table . Need help of how to acheive this in either ways,

 

SELECT 

       DISTINCT R.c_no ,

       SUM(ISNULL(r.MONTHLY_AMT,0)) AS SUM_MONTHLY_AMT , 

       SUM(ISNULL(rd.DISC_AMT,0) )  AS SUM_DISC_AMT ,

       SUM(ISNULL(r.MONTHLY_AMT,0)) - SUM(ISNULL(rd.DISC_AMT,0) )  AS TOTAL_AMOUNT

FROM DBO.Offer R

LEFT JOIN Offer_discount RD

ON RD.c_no = R.c_no

AND RD.ref_no = R.ref_no

GROUP BY R.c_no  

Need Help

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    vijendras

     

    In this scenario, since both tables have multiple entries for same C_No, you have summarize each of them, then you can join them together. You can create calculated tables like below:

     

     

    Table1 =
SUMMARIZE (
    Offer,
    Offer[C_No],
    Offer[Ref_No],
    "SUM_MONTHLY_AMT", SUM ( Offer[MONTHLY_AMT] )
)

     

    Table 2 =
SUMMARIZE (
    Offer_Discount,
    Offer_Discount[C_No],
    Offer_Discount[Ref_No],
    "SUM_DISC_AMT", SUM ( Offer_Discount[DISC_AMT] )
)

    Then you can use LOOKUPVALUE() function to get the [SUM_DISC_AMT] column from Table 2 to Table 1. 

     

    =
LOOKUPVALUE (
    Table2[SUM_DISC_AMT],
    Table2[C_No], Table1[C_No],
    Table2[Ref_No], Table1[Ref_No]
)

    Regards,

     

     

    • vijendras's avatar
      vijendras
      Frequent Visitor
      8 years ago

      Thanks. v-sihou-msftHow can I add a where condition to this ?

       

      similar SQL : 

       

      SELECT 

             DISTINCT R.c_no ,

             SUM(ISNULL(r.MONTHLY_AMT,0)) AS SUM_MONTHLY_AMT , 

             SUM(ISNULL(rd.DISC_AMT,0) )  AS SUM_DISC_AMT ,

             SUM(ISNULL(r.MONTHLY_AMT,0)) - SUM(ISNULL(rd.DISC_AMT,0) )  AS TOTAL_AMOUNT

      FROM DBO.Offer R

      LEFT JOIN Offer_discount RD

      ON RD.c_no = R.c_no

      AND RD.ref_no = R.ref_no

      where r.status = 1 

      GROUP BY R.c_no