Customer Duplicates Rules / Matching

Question

Team,

I have been provided some rules that the business would like to run on our Customer data set.

The rules are as follows ;

FirstName EXACT MatchBlank = FALSE
AND  LastName EXACT MatchBlank = FALSE
AND  MiddleName EXACT MatchBlank = TRUE
AND  Suffix EXACT MatchBlank = TRUE
AND  Email EXACT MatchBlank = FALSE

Matching Method Definition = The exact matching method looks for strings that exactly match a pattern. 
MatchBlank Definition = If you select this option for any field, and that field is blank in both of the records being compared, the fields are considered matches.

I am struggling how to implement such rules. So far i am using the "Fuzzy Match" tool and have the following matching Criteria . But i don't believe i have set the matching rules correctly

Field NameMatch StyleEdit Match OptionsFirstNameCustomGenerate Keys for each word = False

Ignore if empty = False

LastNameCustomGenerate Keys for each word = False

Ignore if empty = False

MiddleNameExact Generate Keys for each word = False

Ignore if empty = True

SuffixExact Generate Keys for each word = False

Ignore if empty = True

EmailCustomGenerate Keys for each word = False

Ignore if empty = False

Have attached example test data along with outcomes

Any sight's / screen shots / working scenarios be much appreciated

Regards

Masond3

Test Data.xlsx

Masond3 · Answer

@peterr_h @This sir is why I love these forums. As I been overthinking this for a while, whilst your solution (need to test when I’m back in the office) seems like it fulfils my requirements

peterr_h · Answer

Hi @Masond3,

I'm not sure if I've misunderstood, but it appears that 'matches' only count if all fields are identical across the two records?

If that's the case, then a simple Join (on all fields), followed by a Filter (to remove a customer joining to themself) should suffice.

From the example given, it only appears that joins are valid when all fields are identical, which a Join on all fields will provide.

I've added a couple of screenshots for your reference.

Please let me know if this is incorrect and I'll do some further work.

Cheers

image.png

Masond3 · Answer

@ChrisTX @Raj @peterr_h  All - apologises for the delay. I have attached scenarios along with expected outcomes. Let me know if your require anything else

many thanks