Fuzzy Join - Data360_Analyze - Latest

Compares two data sets with input fields that may not exactly match, but which are logically correlated and are intended to match.

Fuzzy join algorithms and the criteria defined in the properties are used to match your data (case insensitive).

The following steps guide you through the process of configuring the Fuzzy Joinnode:

1. If you want to restrict the set of records over which the fuzzy join is performed (optional step): In the LeftExactExpr property, add an expression (can be a list of input fields) to define which fields in the left (top) input must exactly match those in the right input. In the RightExactExpr property, add an expression (can be a list of input fields) to define which fields in the right (bottom) input must exactly match those in the left input.

   Note: If you choose to define these optional "Exact" properties, you must define both the LeftExactExpr property and the RightExactExpr property and they must have the same data type. Entries in the left and right pins are grouped according to the values specified in the LeftExactExpr and RightExactExpr properties.

   If you only define the "Exact" properties and do not define the LeftFuzzyExpr and the RightFuzzyExpr properties, a regular join occurs, see Join. If you want to perform a fuzzy join, see the following step.

2. From the FuzzyAlgorithm property, select the algorithm that best suits your data to perform a fuzzy join on the values specified in the LeftFuzzyExpr and RightFuzzyExpr properties.

   There are seven fuzzy join algorithms to choose from, depending on your data:

   Matching algorithms

   • Levenshtein Distance: Calculates the distance in terms of the number of letter substitutions necessary to make one word match another. Records match when the distance is the same or less than the value specified in the FuzzyThreshold property.

     The names "Mark Smith" and "Mack Smith" do not match exactly, but they may represent the same person whose name is entered incorrectly in one source. The fuzzy score is 1, as one letter would need to be substituted to make a match; if the FuzzyThreshold property is 1 or more, they are matched.

   • Transposed Digit: Looks for typographical errors. Records match if the number of transposed characters between the two input fields is the same or less than the value specified in the FuzzyThreshold property.

     In the entries "12345678" and "12435678", "34" and "43" are transposed; there is a fuzzy score of 1. If the FuzzyThreshold property is 1 or more, they are matched.

   • Soundex: Matches by sound using a phonetic algorithm that is catered to the English language. Homophones are encoded to the same representation so that they can be matched despite minor differences in spelling. All non-alphabetical characters, digits, punctuations, and white spaces are ignored.

     The Soundex code for a name consists of a letter followed by three numbers: the letter is the first letter of the name, and the numbers encode the remaining consonants. Similar sounding consonants share the same number, for example, the labial B, F, P and V are all encoded as 1. Vowels are never coded directly unless they appear at the start of the name. Records match if the number of identical characters in the two phonetic representations is the same or more than the value specified in the FuzzyThreshold property.

     For names in unicode or in extended ASCII character set, Soundex considers two characters a match if they are the same.

     Note that in all other algorithms, adding to the threshold creates MORE matches, but in Soundex, adding to the threshold causes a more detailed comparison, and creates LESS matches, in general.

     The Soundex encodings for the names "Jackie" and "Jacquelin" are "J200" and "J245", respectively. The fuzzy score is 2; if the FuzzyThreshold property is 2 or less, the names are matched.

   Distance comparisons

   • Time Distance: Looks for the absolute difference in seconds between two times. Records match if the absolute difference in seconds is the same or less than the value specified in the FuzzyThreshold property.

     The time distance between the following two times is 10 seconds: 02:45:47 and 02:45:57. If the FuzzyThreshold property is 10 or more, they are matched.

   • Date Distance: Looks for the absolute difference in days between two dates. Records match if the absolute difference in days is the same or less than the value specified in the FuzzyThreshold property.

     The date distance between the following two dates is 2 days: 2016-03-03 and 2016-03-05. If the FuzzyThreshold property is 2 or more, they are matched.

   • DateTime Distance: Looks for the absolute difference in seconds between two datetime fields. Records match if the absolute difference between two datetime values is the same or less than the value specified in the FuzzyThreshold property.

     The datetime distance between the following two values is 3661 seconds: 2010-10-10 09:09:09 and 2010-10-10 10:10:10. If the FuzzyThreshold property is 3661 or more, they are matched.

   • Numeric Distance: Looks for the absolute difference between two numbers. Records match if the absolute difference between two numbers is less than the value specified in the FuzzyThreshold property.

     There is a difference of 0.09 between the following two numbers: 10.1 and 10.01. If the FuzzyThreshold property is 0.09 or more, they are matched.

3. Once you have selected a FuzzyAlgorithm, in the FuzzyThreshold property, enter a positive number to specify the threshold that is used to determine if two fields match.
4. In the LeftFuzzyExpr property, type the fields from the left input which are to match those in the right input based on the fuzzy matching criteria.

   In the RightFuzzyExpr property, type the fields from the right input which are to match those in the left input based on the fuzzy matching criteria.

5. If there are fields in the left and right input pins that match exactly, in the RightPrefix property, enter some text to add to all fields from the right input to differentiate them from the left input. This ensures that there are no field naming conflicts in the output. This property is optional.
6. If you want to generate an additional field in the 'uniquematch' and the 'multimatch' outputs that contains the fuzzy scores for the data, in the FuzzyScoreOutputField property, enter a name for the fuzzy score output field. This property is optional; if you do not enter a name, the field will not be included in the output.

The Fuzzy Join node generates two outputs:

• uniquematch: Data that has a one to one match.
• multimatch: Data that has a one to many matches.

You can decide to reduce the multi matches to a single occurrence and then combine with the unique matches, or you can use only the unique matches in the remainder of your analysis.

Optimizing node performance

You can improve the efficiency of the Fuzzy Join node by ensuring that numbers in input data are represented by numeric data types instead of string types, see Data Analyzer (Deprecated). Also, it is recommended to use numeric values for FuzzyExprs or ExactExprs where possible.

The Fuzzy Join node is memory intensive. For best performance, the right input pin should contain the smaller data set.