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.