User Tools

Site Tools


transformations:lookup

LOOKUP

Category: Transform / Basic


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 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

SettingDescription
Lookup tableSelect the dataset that will serve as the lookup table.


Mode settings

SettingDescription
Append new columnA 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

SettingDescription
This tableSelect 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 fromSelect the column in the second table from which values will be returned based on a match in the lookup column.


No conditions met options

SettingDescription
Keep original valueIf a value does not have a match in the lookup table, keep the original value.
Make emptyIf a value does not have a match in the lookup table, make the value empty.
Default toOptions: 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

Example: Substitute all instances of the province name variations with "Quebec" for consistency.

Source 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


Source table 2: Substitution lookup table

Old name New name
Quebec Quebec
QC Quebec
Que. Quebec


Action parameters:

Replace value in column: Province
Using table: Source table 2
Look up in column: Old name
Replace with values from column: New name
When not found: Keep original value


Result table:

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


Community examples


See also

transformations/lookup.txt · Last modified: 2021/07/19 02:31 by craigt