Fill
Fill replaces null values in specified column of a table with the last known non-null value. This operation is forward-only.
Syntax
Fill(pairs...)
Parameters
| Parameter | Type | Description |
|---|---|---|
| pairs | String... | The input/output column name pairs. |
Returns
An UpdateByOperation to be used in an updateBy table operation.
Examples
Forward fill all columns of a table
The following example performs a Fill to replace null values with the most recent non-null. No columns are given to the UpdateByOperation, so the operation is applied to all non-grouping columns in the source table. Also, no grouping columns are given, so the operation is applied to all rows.
source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = (i % 3 == 0) ? NULL_INT : i", "Y = (i % 3 == 1) ? i : NULL_INT")
result = source.updateBy(Fill())
- source
- result
Forward fill all non-key columns
The following example builds on the previous by specifying Letter as the grouping column. Thus, the forward fill is applied to all columns except for Letter, and is done on a per-letter basis.
source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "X = (i % 3 == 0) ? NULL_INT : i", "Y = (i % 3 == 1) ? i : NULL_INT")
result = source.updateBy(Fill(), "Letter")
- source
- result
Forward fill one column grouped by multiple key columns
The following example builds on the previous by specifying Letter and Truth as the grouping columns. Thus, groups are defined by unique combinations of letter and boolean, respectively. The Fill is only applied to the X column, so the Y column has no operations applied to it.
source = emptyTable(10).update("Letter = (i % 2 == 0) ? `A` : `B`", "Truth = randomBool()", "X = (i % 3 == 0) ? NULL_INT : i", "Y = (i % 3 == 1) ? i : NULL_INT")
result = source.updateBy(Fill("X"), "Letter", "Truth")
- source
- result