8 Replies Latest reply: Jan 29, 2017 7:53 PM by Jayant Tibhe RSS

    Need help with Calculated Dimension in Straight Table

      I am not sure if what I am doing is possible.  It is also somewhat difficult to fully articulate but let me try to simplify it.


      I should start by saying, I'm trying to build a Straight Table to show a specific result.


      I have a dataset with the following data structure


      Policy     BaseCode     Type A$     Type B$     Type C$     Type D$

      1               105               $100               $10          $5            $0

      2               201               $500               $200        $0            $50



      I then have a lookup table with the following information


      BaseCode          Type          Code

      105                      A              ABC

      105                      B              XYZ

      105                      C              DEF

      105                      D              JKL

      201                      A              QRS

      201                      B              XYZ

      201                      C              MNO

      201                      D              JKL



      The combination of BaseCode, Type, and Code are not necessarily unique, but the combination of all the Type and Code possibilities to each BaseCode is unique.  That's not necessarily relevant.


      What I want to be able to show from my original table above is this;


      Policy | BaseCode | Type A Code | Type B Code | Type C Code | Type D Code | Type A$ | Type B$ | Type C$

      1        |   105         |        ABC       |      XYZ        |       DEF        |                     |   $100    |   $10      |    $5   

      2        |   201         |       QRS       |      XYZ        |                      |      JKL         |    $500   |    $200   |     $0    


      (edit: I removed Type D $ because the formatting on the website was getting messed up)




      I'm really struggling to figure out how to do this, because when I try to do a calculated dimension, it keeps duplicating the records (I need 1 line per Policy).  Not only that, but it's duplicating all the values so the duplicated results don't make any sense.


      Is it possible to build this?  Again, I'm looking to do it in a straight table without having to do anything to the data itself.