Documentation

    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.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    storeregion_codecodelabel
    OsloNNNorth
    RomeSSSouth
    LimaX
    1. Look at the preview. Oslo gets North and Rome gets South. Lima stays in the table with NULL, because no region has code X.
    2. Change Action to Overwrite Column and set Replace With to label. region_code now 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: Exact

    Options: 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 Columns

    Options: 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.