transformations:lookup
This is an old revision of the document!
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.
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 |
transformations/lookup.1430177446.txt.gz · Last modified: 2015/04/27 19:30 by elenap