Fuzzy Lookup
The Fuzzy Lookup transform joins the columns of two tables into one table by matching key values, where there may not be an exact match between the two tables.
This is similar to the Lookup and Join transforms.
1. Input
The Fuzzy Lookup transform requires two inputs.
The following two tables will be used as an example:
2. Add the transform
Click to select the connector link between two transforms.
In the toolbar, choose Insert Other, then Fuzzy Lookup.
Click the other input transform and drag a connection to the Fuzzy Lookup transform.
To edit/configure the transform, select it and choose Configure in the toolbar.
3. Configure
The Fuzzy Lookup configuration dialog lists the columns from the two input tables.
You will need to designate one or more columns as keys in each table, which will be matched together.
If a relationship between the two tables was already defined, the key elements may already have been set. This will be indicated by a key icon shown next to one or more items in each list, like in the figure above. If you want to remove an existing key binding for your lookup, click the Delete icon next to the key icon.
To define a key binding, drag a column from one input table and drop it onto the corresponding column in the other input table.
The following settings determine how to handle differences between the key values:
- Enter the Probability Threshold. Valid values are from .0001 to 1.0, where a value of 1 will require input data to be an exact match for them to be looked up successfully.
- Set Maximum Matches to determine the maximum number of output records that can be returned when multiple matches are found.
- Select Ignore String Case if you want a non-case-sensitive match.
Uncheck an element in either list to exclude them from the output. You can click Edit output elements at the bottom to rename an element.
4. Output
The figure below illustrates the output in our example.
