LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: losophy

Hi there,

I am trying to match 2 sets of call records, where most of the time there will be a 1 to 1 matching.
However we got stuck because of the below differences of the 2 sets of data:
1) Timestamp will not be exactly the same on both data, although it is the same call record
2) Duration will not be exactly the same on both data, althuogh it is the same call record

I have attached a simple data extract for explanation. For example, we have exact 3 records for a particular Phone A number in both set of data.
However the Timestamp and duration are having slight differences. What we need here is to have best fit match of record to record base on the smallest differences of Duration & Time, e.g. row 3 should rightfully match with row 9, row 4 should match with row 10 and row 5 with row 11. In which maybe PP row 3 record will loop through all 3 records in QQ data to find best fit match.
I can't do sorting or use x-ref because there are not exact matching number of records in both data.
PP_Key and QQ_key are unique identifier of each records. PP_A_B_Number & QQ_A_B_Number are the phone number.

Anyone got some ideas to solve this ? Any help would be welcome, thx in advance!

Date Example.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: losophy

anyone can help with the about situation?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

Losophy, did you try out the lal pack with the fuzzy join?

Here is some info from the doc, does that help?




Fuzzy Join














The Fuzzy Join node matches two sets of case-insensitive and trimmed (leading and trailing spaces) keys which may not match exactly, but are intended to match. It performs a relational joinon the data from two input pins, left and right, using the criteria defined in the parameters to determine matches.
Six Fuzzy algorithms are implemented:
• Levenshtein Distance • Records match if the string distance (minimum number of string edits between two keys to transform one to the other) is less than the FuzzyThreshold.

• Transposed Digit • Records match if the number of transposed characters between two keys is less than the FuzzyThreshold.

• Soundex • Soundex transforms an input string into a phonetic representation. Records match if the number of matched characters in the phonetic representation is more than the FuzzyThreshold.

• Time Distance • Records match if the absolute difference in seconds between two keys of type time is less than the FuzzyThreshold.

• Datetime Distance • Records match if the absolute difference in seconds between to datetime keys is less than the FuzzyThreshold.

• Numeric Distance • Records match if the absolute difference between two numeric keys is less than the FuzzyThreshold.

The Fuzzy Join node has the following paramters. Refer to the parameter documentation for more details. • LeftExactExpr
• RightExactExpr
• FuzzyAlgorithm
• FuzzyThreshold
• LeftFuzzyExpr
• RightFuzzyExpr
• RightPrefix
• FuzzyScoreOutputField
Notes: • Each of the algorithms above takes two keys and a threshold number, and calculates a fuzzy score (numeric "distance") of the two keys. If the fuzzy score is at or below the threshold, the two keys are considered to be a match.
For example, if keys 1000 and 1002 are given to algorithm Numeric Distance, the fuzzy score is 2. If the threshold is 2 or more, these two keys match. Otherwise, the keys do not match.
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.
• For more efficient node performance, it is recommended that numbers in input data are represented in numeric types instead of string types.
---------------------------------------------------------------------------------------
Input and Output
• Input • Each algorithm requires two input data, left and right.
• The keys in left data are compared against the keys in the right data based on the parameter criteria.

• Output • Depending on various conditions, two possible types of outputs: Unique Match and Multi Match.
• Unique Match • This match is very similar to the Multi Match with one restriction: For all key pairs (k1, k2) where k1 comes from the left pin and k2 comes from the right pin, k1 only matches only one key, k2 from the right pin, and k2 matches only one key, k1, from the left pin.

• Multi Match • For each match key pairs (k1, k2) where k1 comes from the left pin and k2 comes from the right pin, if k1 matches one or more keys (in addition to k2) from the right pin as defined by the conditions below, or k2 matches one or more keys (in addition to k1) from the left pin as defined by the conditions below, this match is considered to be multi-match.
• The output will consist of such key pairs and their corresponding fields in the two pins.
• Condition 1: ExactExprs and FuzzyExprs are both defined. • Entries in the left and right pins are grouped according to LeftExactExpr and RightExactExpr.
• From the entries grouped above, keys from the left pin are identified by LeftFuzzyExpr while keys from the right pin are identified by RightFuzzyExpr.
• A fuzzy score is performed on the pairs of these two sets of keys.
• If the fuzzy score between a key-pair is at or below the threshold, such key pair is considered to be a match.

• Condition 2: ExactExprs and FuzzyExprs are both not defined. This is an error.
• Condition 3: ExactExprs are defined and FuzzyExprs are not defined. Contains a regular join of the entries and their corresponding fields in the left and right pins that meet the LeftExactExpr and RightExactExpr criteria.
• Condition 4: FuzzyExpr is defined and ExactExpr is not defined. • Keys from the left pin are identified by LeftFuzzyExpr while keys from the right pin are identified by RightFuzzyExpr.
• A fuzzy score is performed on the pairs of these two sets of keys.
• If the fuzzy score between a key-pair is at or below the threshold, such key pair is considered to be a match.



Fuzzy Node Execution Considerations
The fuzzy node is a memory intensive node, and there are limits to the size of the input data it can handle. For fuzzy joins with only FuzzyExprs are defined, all the data for FuzzyExprs in the right input pin are loaded into memory. In all other cases, all the data for ExactExprs in the right input pin are loaded into memory.
The total of FuzzyExprs or ExactExprs data has to be less than 600MB. Since either FuzzyExpr or ExactExpr of the right input will be loaded into memory, for best performance, the right input pin should contain the smaller data set. Numeric operations are more efficient than string operations. Use numeric values for FuzzyExprs or ExactExprs if possible for better performance. ---------------------------------------------------------------------------------------------------
Examples
• Levenshtein Distance • 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 for these names is 1. If the threshold is 1 or more, they are a match.

• Transposed Digit • The keys "Margaret" and "aMrgarte" have a fuzzy score of 2: "Ma" and "aM" are transposed, and "et" and "te" are transposed. These two keys match if the threshold is 2 or more.

• Soundex • 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 so, for example, the labial B, F, P and V are all encoded as 1. Vowels can affect the coding, but are never coded directly unless they appear at the start of the name. The fuzzy score is the number of characters in the two Soundex encoded strings that are the same. The Soundex encodings for names "Jackie" and "Jacquelin" are "J200" and "J245" respectively, so the fuzzy score is 2. If the threshold is 2 or less, the names "Jackie" and "Jacquelin" match. Notes: (a) 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. (b) Soundex algorithm is catered toward English language. For names in unicode or extended ascii character set, Soundex considers two characters a match if they are the same.

• Time Distance • The fuzzy score for keys "09:09:09" and "09:09:39" is 30 seconds. These two keys match if the threshold is 30 or more.

• Datetime Distance • For datetime timestamps, the distance between the keys "2010-10-10 09:09:09" and "2010-10-10 10:10:10" is 3661 seconds. If the threshold is 3661 or more, these two keys match.

• Numeric Distance • The fuzzy score for the keys 10.1 and 10.01 is 0.09. If the threshold is 0.09 or more, the two keys match.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: losophy

Hi Xathras,

Thanks for the info, yes we have tried using fuzzy x-ref/fuzzy join, however it is not ideally the correct solution, because Threshold is not the main factor here.
By using Threshold, we will be getting mismatch of records, because there are huge time differences between records in QQ database and PP database. What we need, are certain methods to enable best fit match of records, eg. 1st earliest date/time record from PP database matching with 1st earliest date/time record from QQ database, these 2 records will be identifed as match and output as Matched, 2nd record from PP database will loop through all the remaining records in QQ database to match the record with the earliest date/time.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ltolleson

Here is an attempt at a solution for this problem. I was only able to test this with the data you provided and am not certain this will work for all scenarios.

Hope this helps.

Larry
Attachments:
MatchCallRecords.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: losophy

Hi Larry,

Thanks for the solution.
Pardon I have missed out on another requirement, which is beside enable best fit match of records based on the earliest date/time, we need to check on the Duration too, so the logic is below:
1) Enable best fit match of records, eg. 1st earliest date/time record from PP database matching with 1st earliest date/time record from QQ database, these 2 records will be identifed as match and output as Matched, 2nd record from PP database will loop through all the remaining records in QQ database to match the record with the earliest date/time.
2) Follow by checking on the Duration (least difference)

Finally, eg. row 5 of PP static data should match with row 7 QQ static data, row 6 PP static data should match with row 8 QQ static data.
Please find attached graph, i have added additional static data records.
Attachments:
MatchCallRecords.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ltolleson

Here is how I solved this data problem.

1) Convert the date fields for PP and QQ into timestamp types.
2) Add the Duration to the datetime as a number of seconds.
3) Sort the PP and QQ data by A_B_Number and the new Date/Duration field
4) Add a groupCount by grouping of the unique A_B_Numbers. - Not sure this is needed anymore.
5) Using the Fuzzy X-Ref Join the two datasets exactly by A_B_Number do a Fuzzy match on the DT field by datetime.
6) Take the outliers that didn't match and increase the threshold

You can play around with the threshold in the Fuzzy x-ref nodes to get the desired results.

Take a look at the attached graph and see if that is what you need.

Thanks,
Larry
Attachments:
MatchCallRecords.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: losophy

Hi Larry,


Thanks for your quick reply.
Yup it work correctly.
For both PP and QQ records with Duration 1402, I have given a higher duration for easy explanation. In actual cases, there are records with high duration and there are also records with small duration. So when I changed the 2 records from Duration 1402 to a smaller number e.g. 5, there will be duplicates created by the fuzzy x-ref.
I am looking at having some dynamic methods to take care of these different type scenarios, which I can't seems to get a method to resolve all the scenarios.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: losophy

Hi Larry,

Anyway we can work out a dynamic solution for these types of scenarios?

Originally posted by: losophy
					

Hi Larry,



Thanks for your quick reply.

Yup it work correctly.

For both PP and QQ records with Duration 1402, I have given a higher duration for easy explanation. In actual cases, there are records with high duration and there are also records with small duration. So when I changed the 2 records from Duration 1402 to a smaller number e.g. 5, there will be duplicates created by the fuzzy x-ref.

I am looking at having some dynamic methods to take care of these different type scenarios, which I can't seems to get a method to resolve all the scenarios.