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.