2 Replies Latest reply: Nov 28, 2012 2:48 PM by Toney Fidler

# Trouble with IF statement results

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?

• ###### Re: Trouble with IF statement results

change: if(substringcount('C,CF',SDUOM4)=1,...

into:       if(match(SDUOM4,'C','CF'),...

• ###### Re: Trouble with IF statement results

That worked changing the substringcount function with a match function.

Thanks.