nullFill

nullFill(X, Y)

X is a vector/matrix/table.

Y is either a scalar, or a vector/matrix with the same dimension as X.

When X is a table, Y must be a scalar, and the function replaces all NULLs in X with Y. It is especially useful when we would like to replace all NULLs in a table with a certain value, such as -999999. Note that the system will convert the data type of Y to the specified column during the replacement. If Y cannot be converted, an error is raised.

The function always returns a new object. Input X is not altered.

Ex 1. For vectors:

x=1 NULL NULL 6 NULL 7;
nullFill(x,0);
// output
[1,0,0,6,0,7]

y=1..6
nullFill(x,y);
// output
[1,2,3,6,5,7]

Use function nullFill on a vector in a table in a SQL statement:

ID=take(1,6) join take(2,6)
date=take(2018.01.01..2018.01.06, 12)
x=3.2 5.2 NULL 7.4 NULL NULL NULL NULL 8 NULL NULL 11
t=table(ID, date, x)
t;

update t set x=x.nullFill(avg(x)) context by id;
t;

Ex 2. For matrices:

x=1 NULL 2 NULL 3 4 $ 3:2;
x;

x.nullFill(0);

Ex 3. For tables:

t=table(1..6 as id, 2.1 2.2 NULL NULL 2.4 2.6 as x, 4.3 NULL 3.6 6.7 8.8 NULL as y);
nullFill(t, -999999);

Related functions: isNull, hasNull, bfill, ffill, lfill