7 Replies Latest reply on Mar 9, 2016 7:33 AM by Joe Oppelt

# isnull question . Not all the null value is changing to if statement

Hi,

I'm trying to apply "NA" to any null value.

Somehow not all the null value is changing to if statement

What I found is if there is any value for that variable during this 3days period. If won't put "NA" just blank

but if there no value what so ever during 3days period for that variable then it put "NA" ..

How can I apply "NA" to all the null and blank value

Thank you

• ###### 1. Re: isnull question . Not all the null value is changing to if statement

I assume when you mean blank it's actually a zero length string. If this is the case add a new clause to your IF statement, IF [Field] = "" THEN "NA" END

• ###### 2. Re: isnull question . Not all the null value is changing to if statement

If you're pointing to that 0.00 value because you wanted it to be interpreted as null, (and therefore displayed under your "NA" clause), it's not null.  Zero is an actual value, so Tableau is displaying it as 0.00.  If you want zeros to be treated as null, add more to your syntax to account for that.

• ###### 3. Re: isnull question . Not all the null value is changing to if statement

HI Casey,

Zero is the actual value present in the data it is not null value. if you want to zeroes also represented to be as NA then you need to add when zero it need to be NA in your calculation

Change this formula like this:

If AVG(Numeric value )< 0.00 or AVG(Numericvalue) >999

elseif ISNULL(AVG(Numeric Value)) Then "NA"

ELSEIF AVG(Numeric value)=" "  then "NA"

else  "Pass"

end.

• ###### 4. Re: isnull question . Not all the null value is changing to if statement

I'm getting a below issue

" Cant Compare float and string values"

• ###### 5. Re: isnull question . Not all the null value is changing to if statement

How can I fill below area

I used isnull to fill null value which is showing as "NA"

and avg[numeric value]="" doesnt work due to " Cant Compare float and string values"

Is there anyway to fill those area?

• ###### 6. Re: isnull question . Not all the null value is changing to if statement

That's because the value is an actual zero, not a space.  It's a numeric field.  Just check for zero, not space.

The output of the calc field is a string value, but it's based on the contents of a numeric field.

• ###### 7. Re: isnull question . Not all the null value is changing to if statement

As for padding out nulls where no record exists, there are a bunch of threads that discuss this.

Here is one:

Re: Pad number of records with 0 when there are none

Just search for "pad null" on the forum and try out some of the techniques discussed in those threads.  At times it depends on your data and what you are doing in there.

However, if the cell is loaded with a value that is the output of a calc, and if the marks are blank because the result of the calc is a null numeric value, you might be able to correct this simply by wrapping the whole calc in the ZN() function.  That function says to replace null with zero.  (Or, in the calc where you are loading "NA", you can do:

...

ELSEIF ISNULL([Numeric value]) then "NA"

...