Differences

This shows you the differences between two versions of the page.

--- transformations:fillgaps [2015/05/01 13:16] – elenap
+++ transformations:fillgaps [2021/04/05 02:30] – craigt
@@ Line 1: / Line 1: @@
-===== Fill the gaps =====
+{{ transformations:FillGapsAction.png}}
+====== FILL DOWN ======
+Category: Transform / Advanced\\
-This transformation fills the empty cells in selected columns by duplicating the value of a cell above the empty ones.
+\\
-The cells get filled downwards.
+=====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.\\
-EXAMPLE
+\\
+=====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 columns|Select one or more columns to fill empty cells within.|
+|Group by selected columns|Select 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  |
+|Canada  |Alberta  |
-|    | 	British Columbia  |
+|    |British Columbia  |
-|    |    Manitoba  |
+|    |Manitoba  |
-|  USA  |  Alabama  |
+|USA  |Alabama  |
-|    | 	Alaska  |
+|    |Alaska  |
-|    |  Arizona  |
+|    |Arizona  |
+\\
+**Action parameters:**
+> Select "Country" column.
+\\
+**Output:**
-**Objective:** Add the name of the country in empty cells of the column "Country".
+^  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".\\
-**Transformation:** Select the column and calculate.
+**Table:** Staff positions
-**Result:**
+^Row Num ^First Name  ^Last Name  ^Position  ^
+|  1|Bob |Wilson |Admin |
+|  2| |Wilson |Admin |
+|  3|Lisa |Smith |CEO |
+|  4|Darci |Wilson |Accountant |
+|  5| |Wainright |Accountant |
+|  6|Tony |Smith |Accountant |
+|  7| |Smith |Accountant |
+|  8|Roger |Wainright |Accountant |
+|  9| |Smith |Custodial |
+|  10| |Wilson |Admin |
+\\
+**Action parameters:**
+> Fill gaps in "First Name" column
+> Group by "Last Name" and "Position" columns
+\\
+**Output:**
+^Row Num ^First Name  ^Last Name  ^Position  ^
+|  1|Bob |Wilson |Admin |
+|  2|Bob |Wilson |Admin |
+|  3|Lisa |Smith |CEO |
+|  4|Darci |Wilson |Accountant |
+|  5| |Wainright |Accountant |
+|  6|Tony |Smith |Accountant |
+|  7|Tony |Smith |Accountant |
+|  8|Roger |Wainright |Accountant |
+|  9| |Smith |HR Manager|
+|  10|Bob |Wilson |Admin |
-^  Country  ^  State/ Province  ^
+  * "Bob" is filled in where Last Name is "Wilson" //and// Position is "Admin" (rows 2 and 10).\\
-|  Canada  |  Alberta  |
+  * "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.\\
-|  Canada  | 	British Columbia  |
+  * 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.)\\
-|  Canada  |    Manitoba  |
-|  USA  |  Alabama  |
-|  USA  | 	Alaska  |
+\\
-|  USA  |  Arizona  |
+=====See also=====
+  * [[transformations:fillright|Fill right]]