Lookup
Lookup adds columns from a reference table, or overwrites one column. Match the whole value, part of it or its start, ignoring case by default. Every row stays, and only the first match counts. Rows with no match get NULL, or keep their value when you overwrite. The exception is Every Row, which pairs every Target row with every Lookup row.
Label store regions
The Target table has three stores with a region code. The Lookup table turns two of those codes into names.
Lookup interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| store | region_code | code | label |
|---|---|---|---|
| Oslo | N | N | North |
| Rome | S | S | South |
| Lima | X |
- Look at the preview. Oslo gets North and Rome gets South. Lima stays in the table with NULL, because no region has code X.
- Change Action to
Overwrite Columnand set Replace With tolabel.region_codenow reads North, South and X. Lima keeps X because it has no match.
When to use
Use Lookup to add a label from a small reference table, or to correct values against an approved list, when you must not gain or lose rows. If you need to see unmatched rows separately or expect several matches, use Join. For values with typos, use Fuzzy Match.
To keep unmatched rows separately, use Join.
To match values with typos, use Fuzzy Match.
Inputs and outputs
Inputs
2 ports- TargetRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
- LookupRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
- Match
How Target values are matched to the Lookup key. Exact needs the whole value to match. Contains finds the Lookup value anywhere in the Target value. Starts With needs it at the start. Every Row skips matching and pairs every Target row with every Lookup row.
Default:
ExactOptions: Exact, Contains, Starts With, Every Row (cross join).
- Target Column
The Target text column to match on.
- Lookup Column
The Lookup column to compare with the Target column.
- Ignore case
Match without regard to letter case, so "ACME" matches "acme".
Default:
true- Action
Add Columns adds the Lookup columns to each Target row, filled where there's a match. Overwrite Column replaces the value in one Target column where there's a match and leaves the rest of the table as it is.
Default:
Add ColumnsOptions: Add Columns, Overwrite Column.
- Replace With
The Lookup column that overwrites the Target column on a match. Its values are cast to the Target column's type if the types differ.
- Columns
Shape the output: keep, drop, reorder, rename or cast each column. Use the output names: a name found in both inputs ends in _left or _right. Leave empty to keep every column.
Lookup FAQs
- Is Lookup like VLOOKUP?
- Yes, for the common case. It finds each Target value in the Lookup table and uses the first match.
- Can Lookup add or remove rows?
- Every Target row stays and only the first match is used, so the row count doesn't change. The exception is Match set to Every Row, which pairs every Target row with every Lookup row.
- What happens to rows with no match?
- With Add Columns, the new columns are NULL. With Overwrite Column, the row keeps its existing value.
- Does case matter?
- Not by default. Clear Ignore case to match letter case exactly.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.