5 Replies Latest reply: Sep 21, 2016 12:09 PM by Gysbert Wassenaar RSS

    Measure Based on Lookup into another Dimension

      Direct Discovery

      Oracle DB

       

      There are 2 dimensions "Product Store 1" and "Product Store 2" as below.

      Both have same products but different IDs. Both the IDs exist in the FACT table.

       

      Product Store 1

      Product ID Store 1

      Product Name Store 1

      AApple
      BBanana
      CCarrot
      DDill

       

      Product Store 2

      Product ID Store 2

      Product Name Store 2

      WApple
      XBanana
      YCarrot
      ZDill

       

        

      FACT Table

      Product ID Store 1

      Product ID Store 2

      Sales

      AW10
      BX5
      CY20
      DZ60
      BY5
      CX30
      AZ20
      BY40
      DX10
      CZ90
      AW80
      BW30

       

      Report Requirement

      Report has"Product Store Name 1" in the row.
      But the sales should be of "Product Store Name 2" i.e. Lookup the "Product Store Name 1" in Store 2 and get the sales

         

      Report Output

      Product Name Store 1

      Sales(Product ID Store 2)

      Comments

      Apple120Sum of W's
      Banana45Sum of X's
      Carrot65Sum of Y's
      Dill170Sum of Z's

       

       

      Need help!!!