Case to a Negative Number

Reply
Yellow Belt

Case to a Negative Number

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:

 

COUNT(DISTINCT

(CASE

WHEN lte_tbl_return_code.return_reason_description is not Null THEN lte_product_header.product_id * -1 ELSE lte_product_header.product_id

END))

 

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!

Jennifer White, Manager, Information Technology
Mississippi Valley Regional Blood Center
___________________________________________________________
Phone (563) 359-5401 x3976 | Cell (563) 726-2945| jwhite@mvrbc.org
5500 Lakeview Parkway, Davenport, IA 52807| www.bloodcenter.org
Highlighted
Black Belt

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)

end

 

If that doesn't work, then I think you will need to split this into two transforms

1:

count(distinct lte_product_header.product_id)

 

2:

case when lte_tbl_return_code.return_reason_description is not Null THEN 

-1* `Field from step 1` else `Field from step 1`


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.