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 return value from the same row but a different column of that table.
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.
- Used to map values to aggregate categories.
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 #1
Substitute all abbreviations of Quebec with "Quebec".
Before (source table)
Table 1: Canadian Olympic Champions 2014
| Gold medalist | Province | Sport |
|---|---|---|
| Alex Bilodeau | QC | moguls |
| Dara Howell | Ontario | slopestyle |
| Charles Hamelin | Que. | short-track |
Table 2: Quebec abbreviation lookup
| Old name | New name |
|---|---|
| QC | Quebec |
| Que. | Quebec |
After (result table)
| Gold medalist | Province | Sport |
|---|---|---|
| Alex Bilodeau | Quebec | moguls |
| Dara Howell | Ontario | slopestyle |
| Charles Hamelin | Quebec | short-track |
Action parameters
Lookup table: Table 2
Columns that should match: Province (Table 1), Old name (Table 2)
Return from: New name
When not matched: Keep original value
Example #2
Map individual employees to their departments based on their job titles.
Before (source table)
Table 1: Employee list
| Name | Job title |
|---|---|
| Luca | Tech 1 |
| Marta | COO |
| Sam | CFO |
| Sveta | Tech 3 |
| Mike | Tech 2 |
| Jean | HR |
Table 2: Job title to Department mapping
| Title | Dept name |
|---|---|
| CFO | C-Suite |
| COO | C-Suite |
| Tech 1 | IT |
| Tech 2 | IT |
| Tech 3 | IT |
After (result table)
| Name | Job title | Dept name |
|---|---|---|
| Luca | Tech 1 | IT |
| Marta | COO | C-Suite |
| Sam | CFO | C-Suite |
| Sveta | Tech 3 | IT |
| Mike | Tech 2 | IT |
| Jean | HR | HR |
Action parameters
Lookup table: Table 2
Columns that should match: Job title (Table 1), Title (Table 2)
Return from: Dept name
Mode: Append new column
When not matched: Keep original value
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: 2025/01/31 20:29 by craigt