Differences

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

--- transformations:massreplace [2015/04/10 16:05] – elenap
+++ transformations:massreplace [2025/02/02 21:32] (current) – craigt
@@ Line 1: / Line 1: @@
-===== Table-wide replace =====
+{{ transformations:MassReplaceAction.png}}
+====== TABLE-WIDE REPLACE ======
+Category: Transform / Advanced\\
-This transformation performs the same replacement in all or selected columns.
+\\
+=====Description=====
+This action performs the same replacement in all or selected columns.\\
-It's typically used for data cleansing, e.g. for removing word 'NULL' in all columns.
+\\
+=====Use cases=====
+  *Use this action to correct dataset-wide misspellings.
+  *Expand abbreviations into full text, or abbreviate full text values.
+  *Standardize formatting throughout records.
+  *This action is typically used for data cleansing.
+  *Cleaning up the "(not set)" values that Google Analytics uses to represent blank cells.
-EXAMPLE
+\\
+=====Action settings=====
+^Setting^Description^
+|Search for|Enter the value to search for.|
+|where it's|Select whether to match whole-cell values or whole-cell values //and// partial values.\\  Options:  //Entire cell only// or //Entire cell or part of longer text//.|
+|Replace with|Enter the value to replace the search value with.|
+|which is|Select whether the replacement value will occupy the entire cell or just\\ the matching portion of the found search value.  Options:  //Entire cell// or //Matching part//.|
+|Perform replacement in|Select whether to search all or specific columns.  Options: //All columns// or //Selected columns//\\ (and select the column(s) to search from the list).|
-**Source table:** The longest rivers in the world
+\\
+=====Remarks=====
+This action is case-sensitive.  If the //Search for// value does not match values case-wise, the replacement is not made.
-^  River  ^  Length (km)  ^  Continent  ^
+Numeric values can only be searched for, and replaced, as entire cell values.  Upon entering a numeric value in the //Search for// field, only the //Entire cell only// options will be selectable.\\
-| Nile  |  6650  |  Africa  |
-| Amazon  |  6400  |  South America  |
-| Yangtze  |  6300  |  Asia  |
-| Mississippi  |  6275  |  North America  |
-| Yenisei  |  5539  |  Asia  |
-| Yellow River  |  5464  |  Asia  |
-| Ob  |  5410  |  Asia  |
-| Paraná  |  4880  |  South America  |
+\\
+=====Examples=====
-**Objective:** Replace "North America" or "South America" by simply "America"
+====Example #1====
+> Update employee records (email, company name) after "Old Inc." was rebranded to "New Inc."
-**Transformation parameters:**
+===Before (source table)===
+^Name ^Email ^Company ^
+|Alice  |Alice@Old.com  |Old Inc.  |
+|Bob  |Bob@Old.com |Old Inc.  |
+|Carol  |Carol@Old.com  |Old Inc.  |
-  * Search for: America
+===After (result table)===
-  * where it's: Entire cell or part of longer text
+^Name ^Email ^Company ^
-  * Replace with: America
+|Alice  |**Alice@New.com**  |**New Inc.**  |
-  * which is: Entire cell
+|Bob  |**Bob@New.com** |**New Inc.**  |
-  * Perform replacement in: All columns
+|Carol  |**Carol@New.com**  |**New Inc.**  |
-**Output table:**
+===Action parameters===
+>Search for: Old
+>where it's: Entire cell or part of longer text
+>Replace with: New
+>which is: Matching part
+>Perform replacement in: All columns
+\\
+====Example #2====
+>Standardize the Finance Department's name throughout employee records.
+===Before (source table)===
+^Emp ID^ Department ^Salary ($) ^
+|  101|Fin. Dept.  |  70,000|
+|  102|Finance Dept  |  72,000|
+|  103|Fin. Dept.  |  68,000|
+===After (result table)===
+^Emp ID^ Department ^Salary ($) ^
+|  101|**Finance Dept**  |  70,000|
+|  102|Finance Dept  |  72,000|
+|  103|**Finance Dept**  |  68,000|
+===Action parameters===
+>Search for: Fin. Dept.
+>where it's: Entire cell
+>Replace with: Finance Dept
+>which is: Entire cell
+>Perform replacement in: Selected columns
+>Selected columns: Department
+\\
+=====See also=====
+  * [[transformations:Lookup|Lookup]]
+  * [[transformations:match|Match]]
+  * [[transformations:replace|Replace]]
-^  River  ^  Length (km)  ^  Continent  ^
-| Nile  |  6650  |  Africa  |
-| Amazon  |  6400  |  America  |
-| Yangtze  |  6300  |  Asia  |
-| Mississippi  |  6275  |  America  |
-| Yenisei  |  5539  |  Asia  |
-| Yellow River  |  5464  |  Asia  |
-| Ob  |  5410  |  Asia  |
-| Paraná  |  4880  |  America  |