I have the following IF statement:
if(SDUOM='FT' and SDUOM4='EA',SDUPRC/100000,if(substringcount('C,CF',SDUOM4)=1,SDUPRC/1000000,SDUPRC/10000))AS [Unit Price Calc]
I am trying to change the unit price to match the quantity UOM so the correct amount is use later to calculate dollar amount.
If the quantity UOM (SDUOM) = FT and the pricing UOM (SDUOM4) = EA then the unit price is divided by 100000 - What works.
If the pricing UOM (SDUOM4) is either C or CF then the unit price is divided by 1000000 - This doesn't work.
Anything else the unit price is divided by 10000 - everything seems to be defaulting to this if it is not the first expression.
Before I added first expression - SDUOM='FT' and SDUOM4='EA' - both parts of the IF statement worked.
Does anyone have a better way of multi-levle IF statements or what have I done wrong?
