I am having some duplication when I add some dimensions to a straight table that I created. The sum of the dollars is different when I add a dimension which is a part of our product hierarchy. Is there a way to fix this duplication in the script?The totals are not matching when you export to excel and sum the rows.
The numbers that I circled below should match. I used the sum of rows for both to show the differences. The 2018 numbers match exactly and the units for both 2019 and 2018 match. All I did was swap a dimension and the 2019 numbers come out differently in the sum of total rows.
If I remove all the dimensions the 25,395,621 is the total number. That is the correct number and the raw data adds up to that number. Is there a way to prevent the duplication? The data rolls up by UPC and then the hierarchy matches with the UPC. In some cases there is the same UPC with multiple descriptions associated with the same UPC. Is this what could be causing the issue? Is there a way to just load the first matching description to a UPC?
Let me explain two approaches based on my simple fruit/color example.
Create a map table for the UPC-dimension table and use applymap tp get the first value into your transaction table. (I often use this as an alternative to join when I'm not certain about uniqueness of my data)
MapColors: Mapping Load * inline [ Fruit, Color Apple, Red Apple, Green Apple, Yellow Banana, Yellow Strawberry, Red] ; Transactions: Load Fruit, Quantity, ApplyMap('MapColors', Fruit) as Color inline [ Fruit, Quantity Apple, 100 Strawberry, 200 Banana, 300] ;
If you want to keep your UPC data as an dimension table you can choose to only keep the first instance of a dimension value by using firstValue()
FirstValue(Color) as Color
inline [ Fruit, Color Apple, Red Apple, Green Apple, Yellow Banana, Yellow Strawberry, Red] group by Fruit; Transactions: Load * inline [ Fruit, Quantity Apple, 100 Strawberry, 200 Banana, 300];