I have duplications in my data (and I need it). I want to build an "Adjusted Price" column:
1. if line id is duplicated then Adjusted Price would be 0, except the "B" one, that should get the original price.
2. if line id is not duplicated then I want the price to be as the original Price.
3. for lines where B does not exist and there is duplication, then randomly give original price to one and the other ones would get 0.
ultimately it should like this:
Order Id
Modifier
Original Price
Adjusted Price
111
A
100
0
111
B
100
100
111
C
100
0
111
D
100
0
112
B
200
200
112
C
200
0
113
D
170
170
113
C
170
0
113
A
170
0
114
A
125
125
115
C
110
110
115
D
110
0
I have built a column to recognize the duplicated id using:
left join Table:
load Distinct " Order Id", If(Count( "Order Id")>1,1) as Duplicate
resident Discount Group By "Order Id";
and also I have built a formula for this to work, but it doesn't work for all cases:
IF(Original Price- PEEK(Original Price)=0 and duplicate = '1' and Peek("Order Id") = "Order Id" and MODIFIER <> 'B',0, if (duplicate = '1' and MODIFIER <> 'B', Original Price)) AS "Price Adjusted"
