User Tools

Site Tools


transformations:match

MATCH

Category: Transform / Advanced


Description

This action goes through text values of a column in one table and finds matching values in another table (lookup table). Matched lookup values then replace original values. In case of multiple matches, an arbitrary match is taken (in no particular order) and other matches are ignored.


Use cases

The Fuzzy mode of this action can be used for matching text values that may have typos.


Action settings

SettingDescription
ColumnSelect the column in the current dataset that contains the values to match in the lookup table.
Lookup tableSelect the dataset to be use for the lookup.
Lookup columnThis setting appears once the Lookup table has been selected. Select the column that contains the values
to be matched to.
Match modeOptions: Contains, Starts with, Ends with, and Fuzzy. See the table below.
Case sensitive matchWhen checked, text case must match in the lookup values.
If not matchedChoose how unmatched values are handled. Options: Keep existing value, Make empty, or Default to (and choose
whether this value is an expression, or text or a number, and enter the expression or text/number value).


Match mode settings

ModeDescription
ContainsA lookup value is returned if the source value contains it. For example, "tor" would be returned if the source value
was "Motorola".
Starts withA lookup value is returned if the source value starts with it. For example, "Moto" would be returned if the source value
was "Motorola".
Ends withA lookup value is returned if the source value ends with it. For example, "rola" would be returned if the source value
was "Motorola".
FuzzyA lookup value is returned if the source value is within the specified edit distance.
The Damerau–Levenshtein edit distance measures the number of edit operations required to produce one word
from another.
Max. edit distanceThis setting appears when the Match mode is set to "Fuzzy". Choose the edit distance used by the Fuzzy-matching
process. See the Fuzzy mode, above.


Examples

Objective: Match the source value to any of the lookup values. (Illustrates the example statements given above in the Match mode settings table)

Table 1: Source value

Source text
Motorola

Table 2: Lookup table

Lookup values
tor
Moto
rola
Motrola


Action parameters:

Column is "Source text" (in Table 1)
Lookup table is "Table 2"
Lookup column is "Lookup values"
Case sensitive is unchecked.
If not matched, "Make empty"


Using the two tables and parameters above, the following tables show the results of the given settings.

Contains match mode:
Source value
tor


Starts with match mode:
Source value
Moto


Ends with match mode:
Source value
rola


Fuzzy match mode (max edit distance of 1):
Source value
Motrola


See also

transformations/match.txt · Last modified: 2021/04/14 14:33 by dmitry