User Tools

Site Tools


transformations:fillgaps

FILL DOWN

Category: Transform / Advanced


Description

This action fills the empty cells in selected columns by duplicating the value of the last non-empty cell above the empty ones. The cells are filled downwards.


Use cases

  • To fill gaps that may be inherent in an imported outlined or hierarchical data structure.


Action settings

Setting Description
Fill gaps in selected columnsSelect one or more columns to fill empty cells within.
Group by selected columnsSelect the column(s) where matching values will determine what the source value is to fill in.


Examples

Example 1: Add the name of the country in the empty cells of the column "Country".

Table: Countries and States/ Provinces

Country State/ Province
Canada Alberta
British Columbia
Manitoba
USA Alabama
Alaska
Arizona


Action parameters:

Select "Country" column.


Result table:

Country State/ Province
Canada Alberta
Canada British Columbia
Canada Manitoba
USA Alabama
USA Alaska
USA Arizona



Example 2: Fill in empty cells in "First Name" based on the values in "Last Name" and "Position".

Table: Staff positions

Row Num First Name Last Name Position
1Bob Wilson Admin
2 Wilson Admin
3Lisa Smith CEO
4Darci Wilson Accountant
5 Wainright Accountant
6Tony Smith Accountant
7 Smith Accountant
8Roger Wainright Accountant
9 Smith Custodial
10 Wilson Admin


Action parameters:

Fill gaps in "First Name" column
Group by "Last Name" and "Position" columns


Result table:

Row Num First Name Last Name Position
1Bob Wilson Admin
2Bob Wilson Admin
3Lisa Smith CEO
4Darci Wilson Accountant
5 Wainright Accountant
6Tony Smith Accountant
7Tony Smith Accountant
8Roger Wainright Accountant
9 Smith HR Manager
10Bob Wilson Admin
  • "Bob" is filled in where Last Name is "Wilson" and Position is "Admin" (rows 2 and 10).
  • "Tony" is filled in where Last Name is "Smith" and Position is "Accountant" (row 7). It is not filled in row 9 as Position is "HR Manager" and no previous records have a First Name value for the Last Name = "Smith" and Position = "HR Manager" combination.
  • No value is filled in row 5 as there is no Last Name = "Wainright" and Position = "Accountant" record prior to the empty cell. (The First Name of "Roger" - row 8 - falls after the empty value - row 5.)


Community examples


See also

transformations/fillgaps.txt · Last modified: 2021/07/18 01:36 by craigt