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?

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

 Program PERSON ID First Name Last Name Credits Subject 12345 Jenny Dare 2 Math 678910 Bob Dane 3 English English Literature 69384 Sofia Kristin 5 English Math Education 659412 Ginger Root 2 Math
• ###### 1. Re: How to assign a name to Nulls in a calculated field?

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?

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?

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?

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?

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?

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!!