6 Replies Latest reply on Feb 10, 2016 8:26 AM by Oxana Jomiru

    How to assign a name to Nulls in a calculated field?

    Oxana Jomiru

      I have this data sample below. I created a calculated field names "Program associated" in the enclosed workbook. The calculated field accounts for every subject and Program, however, I am not sure how to capture the Nulls. I would like to assign the null values programs accordingly. Thank you for your help!!

       

       

      Here is my calculated field which you will also see in the enclosed workbook.

       

      IF [Subject]="English" AND CONTAINS([Program],"English Literature") THEN "English Literature and Language"

      ELSEIF [Subject]="Math" AND CONTAINS([Program],"Math Education")THEN "Mathematics"

      ELSE [Program]

      END

       

      ProgramPERSON IDFirst NameLast NameCreditsSubject
      12345JennyDare2Math
      678910BobDane3English
      English Literature69384SofiaKristin5English
      Math Education659412GingerRoot2Math
        • 1. Re: How to assign a name to Nulls in a calculated field?
          Tina Hauser

          Hi Oxana!

          Use the IFNULL function in your calculated field:

           

          IF [Subject]="English" AND CONTAINS([Program],"English Literature") THEN "English Literature and Language"

          ELSEIF [Subject]="Math" AND CONTAINS([Program],"Math Education")THEN "Mathematics"

          ELSE IFNULL([Program],'No program')

          END

          1 of 1 people found this helpful
          • 2. Re: How to assign a name to Nulls in a calculated field?
            Oxana Jomiru

            Hi Tina,

             

            I did try that before, but the issue with that is that it takes all nulls in one "bulk" and names them one thing (whatever I give it". What I need is the following:

            If subject=English and if the Program is null, then "English Literature"

            If subject=Math and if the program is null, then "Math Education"

             

            This is what I've tried and it didn't work:

             

            IF [Subject]="English" AND CONTAINS([Program],"English Literature") THEN "English Literature and Language"

            ELSEIF [Subject]="Math" AND CONTAINS([Program],"Math Education")THEN "Mathematics"

            ELSEIF [Subject]="English" AND IFNULL [Program] THEN "English Literature and Language"

            ELSEIF [Subject]="Math" AND IFNULL [Program] THEN "Mathematics"

            ELSE [Program]

            END

             

            In other words, i need to give individual names to the null programs based on the subject value that is available. Does this make sense? Would you happen to know how to use IFNULL in this case?

            • 3. Re: How to assign a name to Nulls in a calculated field?
              Will Conklin

              Hi Oxana,

               

              It looks like you're using the function IFNULL instead of ISNULL. IFNULL says "If this is not null, give me this, else this." ISNULL returns a boolean.

               

              Based on the structure of IF statements, if you go through and assign the subjects with programs first, then assign the subjects with null projects program associations, that may work.

               

              Something like this:

               

              IF [Subject]="English" AND CONTAINS([Program],"English Literature") THEN "English Literature and Language"

              ELSEIF [Subject]="English" THEN "English Literature"

              ELSEIF [Subject]="Math" AND CONTAINS([Program],"Math Education")THEN "Mathematics"

              ELSEIF [Subject]="Math" THEN "Math Education"

              ELSE [Program]

              END

               

              Cheers,

               

              Will

              • 4. Re: How to assign a name to Nulls in a calculated field?
                Tina Hauser

                The IFNULL function has 2 pieces - first you specify which field you're checking (program) and then the 2nd piece tells it what to do if that field is null.

                 

                For what you've described, the calculation would look like this:

                IF [Subject]="English"

                    THEN IF CONTAINS([Program],"English Literature") THEN "English Literature and Language"

                    ELSE IFNULL([Program],"English Literature and Language")

                    END

                ELSEIF [Subject]="Math"

                    THEN IF CONTAINS([Program],"Math Education")THEN "Mathematics"

                    ELSE IFNULL([Program],"Mathematics")

                    END

                ELSE [Program]

                END

                 

                However - if the Program Association will always be "English Literature and Language" if the Subject is "English" then you really don't need this much logic. Just simply say if it's English, make it English Literature and Language, and don't worry about the Program. But, if the Program value could change your end result then you could use something like what I've pasted in above.

                 

                Hope this helps!

                Tina

                1 of 1 people found this helpful
                • 5. Re: How to assign a name to Nulls in a calculated field?
                  Oxana Jomiru

                  Brilliant, thank you!!! I didn't know about the two pieces of the IFNULL function. This makes a lot of sense! Thank you for also explaining it to me. I appreciate it.

                  • 6. Re: How to assign a name to Nulls in a calculated field?
                    Oxana Jomiru

                    Hi Will,

                     

                    Thank you for the tip. It looks like Tina's suggestion nailed it. I actually have to take into account both the subject and the program. There is a lot of data, thus I can't go through and assign the subjects with programs. By using Tina's suggested formula, I will accomplish exactly what I was aiming for. Thank you so much for chiming in though! Learning something new every day with Tableau functions, literally.

                     

                    Much appreciated!!