ifnull(X;Y)

Returns a specified value when the value in a given column is N/A.

Syntax

ifnull(X;Y)

Input

Argument Type Description
X any simple type A column name
Y any simple type A scalar value or the name of a column
Note: X and Y must be the same type.

Return Value

Returns Y if the value in column X is N/A. Otherwise, returns the value in column X.

Sample Usage

X Y ifnull(X;Y)
NA 1 1
1 2 1
999 NA 999

Example

Let's use the table pub.demo.weather.wunderground.observed_daily to illustrate the use of ifnull(X;Y).

You can see that some of the values in the snowfallm and snowfalli columns contain N/A values.

Let's say we want to change those N/A values to 0 for computational purposes, but we want to keep all the other values the same. To do this, we can create a new computed column that contains the desired results by using the following Macro Language code:
<willbe name="snowfallm_nona" value="ifnull(snowfallm;0)"/>
<willbe name="snowfalli_nona" value="ifnull(snowfalli;0)"/>
Then, just to make it easier to see the results, let's use <colord> to display the columns so that we can see the two original columns next to the two new columns we just created:
<colord cols="date,snowfallm,snowfalli,snowfallm_nona,snowfalli_nona"/>

This will give us the following:

You can see for those items in snowfallm and snowfalli that were N/A, they now contain the value 0 in the snowfallm_nona and snowfalli_nona columns, respectively.