transformations:lookup
Table of Contents
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
Setting | Description |
---|---|
Lookup table | Select the dataset that will serve as the lookup table. |
Mode settings
Setting | Description |
---|---|
Append new column | A 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
Setting | Description |
---|---|
This table | Select 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 from | Select the column in the second table from which values will be returned based on a match in the lookup column. |
No conditions met options
Setting | Description |
---|---|
Keep original value | If a value does not have a match in the lookup table, keep the original value. |
Make empty | If a value does not have a match in the lookup table, make the value empty. |
Default to | Options: 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
- How to look up and replace a string of text (Project; Module: Main; Group: Tab 1; Table: Table 3; Action position: 4)
- Joining different pairs of value equivalences into “global equivalences” (Project; Module: Group; Group: Tab 1; Table: Result;
Action position: 2) - Search, remove, and shift (Project; Module: Main; Group: Regrouping; Table: ExtractingData.xlsx; Action position: 12)
See also
transformations/lookup.txt · Last modified: 2021/07/19 02:31 by craigt