I have a beast mode to calculate the distinct count of products from transactions. These transactions can represent shipments or returns. If the transaction is a return, I want to case the distinct count of the products to a negative number. I currently have the following but it is only returning positive values:
WHEN lte_tbl_return_code.return_reason_description is not Null THEN lte_product_header.product_id * -1 ELSE lte_product_header.product_id
Attached is a screenshot of the data. Those that have the return for reissue return reason should be returning a negative number.
Thank you in advance for any suggestions!
The problem here is the aggregate that you are using.
You are counting the distinct number of Product IDs. This count would be the same regardless of a positive or negative value.
You should try something like this:
case when lte_tbl_return_code.return_reason_description is not Null
then -1* count(distinct lte_product_header.product_id)
else count(distinct lte_product_header.product_id)
If that doesn't work, then I think you will need to split this into two transforms
case when lte_tbl_return_code.return_reason_description is not Null THEN
-1* `Field from step 1` else `Field from step 1`