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

    Trouble with IF statement results

    Toney Fidler

      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?