Differences

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

--- transformations:lookup [2014/10/25 17:23] – created dmitry
+++ transformations:lookup [2021/04/04 00:01] – 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 not found three options available:
+When the value is not found, three options are available:
   * Keep old value
   * Leave cell empty
   * Put some predefined (default) value
+\\
+=====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  ^
+| Dara Howell  |Ontario  |women’s slopestyle  |
+| Justine Dufour-Lapointe  |Quebec  |women’s moguls  |
+| Kaillie Humphries  |Alberta  |women’s bobsleigh  |
+| Alex Bilodeau  |QC  |men’s moguls  |
+| Heather Moyse  |P.E.I.  |women’s bobsleigh  |
+| Charles Hamelin  |Que.  |men’s short-track  |
+\\
+**Table 2:** Substitution lookup table
+^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:**
+^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:
+^Gold medalist  ^Province  ^Sport  ^New name^
+| Dara Howell  |Ontario  |women’s slopestyle  |Ontario  |
+| Justine Dufour-Lapointe  |Quebec  |women’s moguls  |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  |
+\\
+=====See also=====
+  * [[transformations:merge|Merge another table]]
+  * [[transformations:replacecolumns|Modify columns(s)]]
+  * [[transformations:rule|Rule]]