How to treat nulls as zeros for purpose of adding?

[atornes]

I'm trying to add 2 fields, each of which contains some nulls. How can I treat these nulls as zeros for the purpose off adding? When doing so as is, it the 2 values added together have returned null.

There are no good examples of how to use the if() operator and the ifnull() operator has no documentation at all. I'd imagine these are the best methods for achieving this. Any help would be appreciated

[dwaddle]

The eval command coalesce is also useful for this purpose. coalesce will return the first non-null value from its set of arguments - using it is frequently easier than cascading if statements.

| eval xyz=coalesce(field1,field2,field3,0)

[rtadams89]

Coalesce is woefully underutilized and a great suggestion. However, as you have it here, it will produce a significantly different result than what the if(isnull()) commands I posted above will do, and significantly different than what atornes was looking for. Again though, props for remembering coalesce.

If you really want to use coalesce in this situation you would need:

| eval field1=coalesce(field1, 0) | eval field2=coalesce(field2, 0) |

[rtadams89]

If you have two fields ("field1" and "field2"), you could put this in your search query before the addition is performed:

| eval field1=if(isnull(field1), 0, field1 | eval field2=if(isnull(field2), 0, field2) |

There are slightly more compact ways to do the same thing, but the above should perform fine and will keep your search string easy to read.

[Ravan · Accepted solution]

Try this in the query --> fillnull value=0

[rtadams89]

This is one of the more compact ways to do it. I would include the optional field parameters too, as you don't want to accidentaly set some fields equal to zero that should remain null. Also, it should default to "0", so the "value" parameter is optional.

| fillnull field1 field2 |