Fuzzy Lookup

Contents[Hide]

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.

Transform - Fuzzy Lookup
Transform - Fuzzy Lookup

1. Input

The Fuzzy Lookup transform requires two inputs.

The following two tables will be used as an example:

Example inputs
Example inputs

2. Add the transform

Click to select the connector link between two transforms.

Select a connector link
Select a connector link

In the toolbar, choose Insert Other, then Fuzzy Lookup.

Adding from the toolbar
Adding from the toolbar

Click the other input transform and drag a connection to the Fuzzy Lookup transform.

Connecting the second input
Connecting the second input

To edit/configure the transform, select it and choose Configure in the toolbar.

Configure the transform
Configure the transform

3. Configure

The Fuzzy Lookup configuration dialog lists the columns from the two input tables.

Fuzzy Lookup transform configuration
Fuzzy Lookup transform configuration

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.

Fuzzy lookup output
Fuzzy lookup output

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri