FILL Function

• The row from which to extract a value is determined by the order in which the rows are organized at the time that the transform is executed. If you are working on a randomly generated sample of your dataset, the values that you see for this function might not correspond to the values that are generated on the full dataset during job execution.

• The function accepts a second Integer parameter because it is a window transform function. However, this parameter is not used.
• You can use the group and order parameters to define the groups of records and the order of those records to which this transform is applied.
• This function works with the following transforms:
  • Window Transform
  • Set Transform
  • Derive Transform

Basic Usage

window value:FILL(myNumber) order:'Date'

Output: Generates a new column, which contains all values from the myNumber column with any null cells filled by the most recent non-blank value, as determined by the Date column.

Syntax and Arguments

window value:FILL(col_ref, [k_integer]) order: order_col [group: group_col]

For more information on the order and group parameters, see Window Transform.

For more information on syntax standards, see Language Documentation Syntax Notes.

col_ref

Name of the column whose values are filled when null. 

• Multiple columns and wildcards are not supported.

Usage Notes:

k_integer

Usage Notes:

Examples

Example - Fill with quarterly forecast values

Your monthly sales data includes amount sold for each month. However, the source system only provides the quarterly forecast as part of the data for the first month of each quarter. You can use the FILL function to add the prior forecast to each month's data. 

Source:

Transform:

You can use the following transform to fill the prior forecast value for each blank month in the Forecast_Qtr column. Note that the order parameter must be set to Date to establish the proper sorting:

window value: FILL(Forecast_Qtr) order: Date

You can now drop the Forecast_Qtr column and rename the generated window column to the dropped name.

To see how you are progressing each month, you might use the following transform, which computes the average forecast for each month:

derive type:single value:NUMFORMAT((Forecast_Qtr/3),'####.##') as:'Forecast_Month_Avg'

You can then compare this value to the actual Amount value for each month:

derive type:single value:NUMFORMAT(((Amount - Forecast_Month_Avg)/Forecast_Month_Avg)*100, '##.00') as:'MonthActualVForecast_Pct'

Results: