3 Replies Latest reply: Jul 29, 2011 12:28 AM by Erich Shiino RSS

    If Statement and Null Value Problems

      Hi guys, I have a small issue with an If statement and null values.

       

      I have a range of values which are banded up based on another field.

       

      The original field contains numbers between 0 and 15. The requirement is to include only numbers between 0 and 10 and exclude all numbers above 10.

       

      Example

      IF([Value 1] > 0 and [Value 1] < 5, '< 5',

           IF([Value 1] >= 5 and [Value 1] <10, >5'))  as New_Value.

       

      I have no else part in my If statement.

       

      When I use my new field called New_Value in a table, I try to calculate percentages and I find out that my percentages don't sum up to 100% when I use only <5 and >5, because there are some null values which correspond to values above 10 that I have excluded in my load script.

       

      Is there a way I can get rid of all the unwanted values above 10, so that when i work out percentages in my table, my totals will sum up to 100% for only values between 0 and 10?

       

       

      Thank you