nullFill
Syntax
nullFill(X, Y)
Arguments
X is a vector/matrix/table.
Y is either a scalar, or a vector/matrix with the same dimension as X.
Details
-
If Y is a scalar: replace the NULLs in X with Y.
-
If Y is a vector/matrix : replace the NULLs in X with the values of corresponding elements in Y.
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.
Examples
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;
ID | date | x |
---|---|---|
1 | 2018.01.01 | 3.2 |
1 | 2018.01.02 | 5.2 |
1 | 2018.01.03 | |
1 | 2018.01.04 | 7.4 |
1 | 2018.01.05 | |
1 | 2018.01.06 | |
2 | 2018.01.01 | |
2 | 2018.01.02 | |
2 | 2018.01.03 | 8 |
2 | 2018.01.04 | |
2 | 2018.01.05 | |
2 | 2018.01.06 | 11 |
update t set x=x.nullFill(avg(x)) context by id;
t;
ID | date | x |
---|---|---|
1 | 2018.01.01 | 3.2 |
1 | 2018.01.02 | 5.2 |
1 | 2018.01.03 | 5.266667 |
1 | 2018.01.04 | 7.4 |
1 | 2018.01.05 | 5.266667 |
1 | 2018.01.06 | 5.266667 |
2 | 2018.01.01 | 9.5 |
2 | 2018.01.02 | 9.5 |
2 | 2018.01.03 | 8 |
2 | 2018.01.04 | 9.5 |
2 | 2018.01.05 | 9.5 |
2 | 2018.01.06 | 11 |
Ex 2. For matrices:
x=1 NULL 2 NULL 3 4 $ 3:2;
x;
#0 | #1 |
---|---|
1 | |
3 | |
2 | 4 |
x.nullFill(0);
#0 | #1 |
---|---|
1 | 0 |
0 | 3 |
2 | 4 |
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);
id | x | y |
---|---|---|
1 | 2.1 | 4.3 |
2 | 2.2 | -999999 |
3 | -999999 | 3.6 |
4 | -999999 | 6.7 |
5 | 2.4 | 8.8 |
6 | 2.6 | -999999 |