Category: Transform / Basic
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:
Setting | Description |
---|---|
Lookup table | Select the dataset that will serve as the lookup table. |
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. |
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. |
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. |
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 |