Differences

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

--- transformations:lookup [2018/08/15 14:21] – dmitry
+++ transformations:lookup [2021/07/17 16:11] – [Examples] craigt
@@ Line 1: / Line 1: @@
-===== Lookup =====
+{{ transformations:LookupAction.png}}
+====== LOOKUP ======
+Category: Transform / Basic\\
-This transformations looks up values from a specified column in another table and, if found, returns value from the same row but different column of that table.
+\\
+=====Description=====
+This action looks up values from a specified column in another table and if found, a returns value from the same row but different column of that table.
-When value is not found, three options are available:
+When the value is not found, three options are available:
   * Keep old value
   * Leave cell empty
   * Put some predefined (default) value
-EXAMPLE
+\\
+=====Use cases=====
+  * Used to replace common misspellings with the correct value.
+  * Used to replace abbreviations with the full value.
+  * Used to replace numeric codes with descriptive text values.
+\\
+=====Action settings=====
+^Setting^Description^
+|Lookup table|Select the dataset that will serve as the lookup table.|
+\\
+====Mode settings====
+^Setting^Description^
+|Append new column|A new column (named "New name") will be created in the current dataset to hold the result of the lookup.|
+|Replace in (column)|Upon a successful match in the lookup table, the return value will //replace// the original value in the current dataset.|
+\\
+====Columns that should match settings====
+^Setting^Description^
+|This table|Select the column in the current dataset containing values that will be matched in the foreign dataset.|
+|(Look up in column)|The column in the second dataset that will be used to find matching values in the current dataset.|
+|Return from|Select the column in the second table from which values will be returned based on a match in the lookup column.|
+\\
+====No conditions met options====
+^Setting^Description^
+|Keep original value|If a value does not have a match in the lookup table, keep the original value.|
+|Make empty|If a value does not have a match in the lookup table, make the value //empty//.|
+|Default to|Options:  //it's an expression// or //it's a text or number//.  Enter either an expression to be evaluated or a static value\\ to be used in cases where a value is not found in the lookup table.|
+\\
+=====Examples=====
+**Objective:** Substitute all instances of the province name variations with "Quebec" for consistency.
 **Table 1:** Canadian Olympic Champions 2014
-^  Gold medalist  ^  Province  ^  Sport  ^
+^Gold medalist  ^Province  ^Sport  ^
-| Dara Howell  |  Ontario  |  women’s slopestyle  |
+| Dara Howell  |Ontario  |women’s slopestyle  |
-| Justine Dufour-Lapointe  |  Quebec  |  women’s moguls  |
+| Justine Dufour-Lapointe  |Quebec  |women’s moguls  |
-| Kaillie Humphries  |  Alberta  |  women’s bobsleigh  |
+| Kaillie Humphries  |Alberta  |women’s bobsleigh  |
-| Alex Bilodeau  |  QC  |  men’s moguls  |
+| Alex Bilodeau  |QC  |men’s moguls  |
-| Heather Moyse  |  P.E.I.  |  women’s bobsleigh  |
+| Heather Moyse  |P.E.I.  |women’s bobsleigh  |
-| Charles Hamelin  |  Que.  |  men’s short-track  |
+| Charles Hamelin  |Que.  |men’s short-track  |
+\\
+**Table 2:** Substitution lookup table
-**Objective:** Substitute all instances of the province name deviations with "Quebec" for consistency.
+^Old name  ^New name  ^
+| Quebec  |Quebec  |
+| QC  |Quebec  |
+| Que.  |Quebec  |
+\\
+**Action parameters:**
+> Replace value in column: Province
+>Using table: Table 2
+>Look up in column: Old name
+>Replace with values from column: New name
+>When not found: Keep original value
+\\
+**Result:**
-**Table 2:** Substitution look-up table
+^Gold medalist  ^Province  ^Sport  ^
+| Dara Howell  |Ontario  |women’s slopestyle  |
+| Justine Dufour-Lapointe  |Quebec  |women’s moguls  |
+| Kaillie Humphries  |Alberta  |women’s bobsleigh  |
+| Alex Bilodeau  |Quebec  |men’s moguls  |
+| Heather Moyse  |P.E.I.  |women’s bobsleigh  |
+| Charles Hamelin  |Quebec  |men’s short-track  |
+\\
+**Alternate result:**\\
+If "Append new column" had been selected instead of "Replace value",above, the following dataset would be the output:
-^  Old name  ^  New name  ^
+^Gold medalist  ^Province  ^Sport  ^New name^
-| Quebec  |  Quebec  |
+| Dara Howell  |Ontario  |women’s slopestyle  |Ontario  |
-| QC  |  Quebec  |
+| Justine Dufour-Lapointe  |Quebec  |women’s moguls  |Quebec  |
-| Que.  |  Quebec  |
+| Kaillie Humphries  |Alberta  |women’s bobsleigh  |Alberta  |
+| Alex Bilodeau  |QC  |men’s moguls  |Quebec  |
+| Heather Moyse  |P.E.I.  |women’s bobsleigh  |P.E.I.  |
+| Charles Hamelin  |Que.  |men’s short-track  |Quebec  |
-**Transformation:**
+\\
-  * Replace value in column: Province
+====Community examples====
-  * Using table: Table 2
+  * [[https://community.easymorph.com/t//1551/2|How to look up and replace a string of text]] ([[https://community.easymorph.com/uploads/short-url/dvCtD7gg0AHUtXZ7tmQmuHflSYU.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 3//; Action position: //4//)
-  * Look up in column: Old name
+  * [[https://community.easymorph.com/t//1837/4|Joining different pairs of value equivalences into “global equivalences”]] ([[https://community.easymorph.com/uploads/short-url/1TpBXIjf6r4auDHlOBejKh2rSTw.morph|Project]]; Module: //Group//; Group: //Tab 1//; Table: //Result//;\\ Action position: //2//)
-  * Replace with values from column: New name
+  * [[https://community.easymorph.com/t//2060/2|Search, remove, and shift]] ([[https://community.easymorph.com/uploads/short-url/cCqOTak9SLKPXbityR5IAlgULJa.morph|Project]]; Module: //Main//; Group: //Regrouping//; Table: //ExtractingData.xlsx//; Action position: //12//)
-  * When not found: Keep original value
-**Result:**
+\\
+=====See also=====
+  * [[transformations:merge|Merge another table]]
+  * [[transformations:replacecolumns|Modify columns(s)]]
+  * [[transformations:rule|Rule]]
-^  Gold medalist  ^  Sport  ^  Province  ^
-| Dara Howell  |  women’s slopestyle  |  Ontario  |
-| Justine Dufour-Lapointe  |  women’s moguls  |  Quebec  |
-| Kaillie Humphries  |  women’s bobsleigh  |  Alberta  |
-| Alex Bilodeau  |  men’s moguls  |  Quebec  |
-| Heather Moyse  |  women’s bobsleigh  |  P.E.I.  |
-| Charles Hamelin  |  men’s short-track  |  Quebec  |