Differences

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

--- transformations:lookup [2015/01/29 23:20] – dmitry
+++ transformations:lookup [2018/08/15 14:21] – dmitry
@@ Line 1: / Line 1: @@
-===== Map =====
+===== Lookup =====
-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.
+This action 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.
-When value not found three options available:
+When value is not found, three options are available:
   * Keep old value
   * Leave cell empty
   * Put some predefined (default) value
+EXAMPLE
+**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  |
+**Objective:** Substitute all instances of the province name deviations with "Quebec" for consistency.
+**Table 2:** Substitution look-up table
+^  Old name  ^  New name  ^
+| Quebec  |  Quebec  |
+| QC  |  Quebec  |
+| Que.  |  Quebec  |
+**Transformation:**
+  * 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  ^  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  |