transformations:lookup
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
transformations:lookup [2014/10/25 17:23] – created dmitry | transformations:lookup [2015/04/27 19:30] – elenap | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Lookup | + | ===== Map ===== |
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 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. | ||
- | When value not found three options available: | + | When value is not found, three options |
* Keep old value | * Keep old value | ||
* Leave cell empty | * Leave cell empty | ||
* Put some predefined (default) value | * Put some predefined (default) value | ||
+ | |||
+ | EXAMPLE | ||
+ | |||
+ | **Table 1:** Canadian Olympic Champions 2014 | ||
+ | |||
+ | ^ Gold medalist | ||
+ | | Dara Howell | ||
+ | | Justine Dufour-Lapointe | ||
+ | | Kaillie Humphries | ||
+ | | Alex Bilodeau | ||
+ | | Heather Moyse | P.E.I. | ||
+ | | Charles Hamelin | ||
+ | |||
+ | **Objective: | ||
+ | |||
+ | **Table 2:** Substitution look-up table | ||
+ | |||
+ | ^ Old name ^ New name ^ | ||
+ | | 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 | ||
+ | | Dara Howell | ||
+ | | Justine Dufour-Lapointe | ||
+ | | Kaillie Humphries | ||
+ | | Alex Bilodeau | ||
+ | | Heather Moyse | women’s bobsleigh | ||
+ | | Charles Hamelin | ||
+ |
transformations/lookup.txt · Last modified: 2021/07/19 02:31 by craigt