3 Replies Latest reply on Feb 8, 2012 10:37 PM by Alex Blakemore

    How can I check for Null in an If Statement?

    . Tableautester

      I would like to create a custom calc to check for Null inside of an If Statement. Anyone know how to get it to work using IFNULL or is there different way that is better?

       

      This is what I am trying:

      IF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

      ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

      ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

      ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

      ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText2") = true) THEN "MyCategory2"

      ELSEIF( IFNULL([CUSTOM_CLASSIFICATION],1)      ) THEN "MyCategory1"

      END

       

      Here is what I am using in the mean time:

      IF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

      ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

      ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

      ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

      ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText2") = true) THEN "MyCategory2"

      ELSE "MyCategory1"

      END

        • 1. Re: How can I check for Null in an If Statement?
          Matt York

          Try ISNULL instead of IFNULL.

           

          IF(CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

          ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

          ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

          ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText1") = true) THEN "MyCategory1"

          ELSEIF( CONTAINS([CUSTOM_CLASSIFICATION],"MyText2") = true) THEN "MyCategory2"

          ELSEIF(ISNULL([CUSTOM_CLASSIFICATION])) THEN "MyCategory1"

          END

          3 of 3 people found this helpful
          • 2. Re: How can I check for Null in an If Statement?
            Shawn Wallwork

            Have you considered going the other way and using ZN()?

            Not that I know how or if this would work; really just a thought.

             

            --Shawn

            • 3. Re: How can I check for Null in an If Statement?
              Alex Blakemore

              contains(null, "any text") will evaluate as false, so if you want "MyCategory1" to be the default, including the null case, then your second "in the meantime" solution is all you need. You don't have to explicitly test for null in this case, but if you ever do, isnull() is the function to use.

               

              BTW, you don't need the "= true" on each line. Its redundant. Same with the outer parenthesis on each line. Personally, I find it a lot easier to read code if you leave out extra punctuation and use lower case as much as possible, such as keywords and function names. Most people don't write English text in all caps for a reason. Unfortunately, case is significant for field names though.

              1 of 1 people found this helpful