User Tools

Site Tools


transformations:lookup

Lookup

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 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.txt · Last modified: 2018/08/15 18:21 by dmitry