Trouble with IF statement results

tfidler240 — Wed, 28 Nov 2012 15:50:11 GMT

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

Gysbert_Wassenaar — Wed, 28 Nov 2012 15:54:52 GMT

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

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

tfidler240 — Wed, 28 Nov 2012 19:48:57 GMT

That worked changing the substringcount function with a match function.

Thanks.