EMAIL matching

[rmwillis1973]

apologies for this post, but my mind is totally frazzled and i can't seem to get to the right answer.

I've done some fuzzy matching of customer addresses to produce a parent ID.

I now need to do some matching of email addresses, but it keeps when i get a match between 2 emails it over-writing the Parent ID.

PARENT ID	EMAIL	WHAT PARENT ID SHOULD BE	PARENT ID OVER-WRITTEN
123456	R.M.WILLIS@BTOPENWORLD.COM	123456	NO
123678	R.M.WILLIS@BTOPENWORLD.COM	123456	NO
224466	DAVE@GMAIL.COM	224466	NO
456789	SUE@GMAIL.COM	123456	YES
908080	R.M.WILLIS@BTOPENWORLD.COM	123456	YES
908080	SUE@GMAIL.COM	123456	YES
453423	RAY@GMAIL.COM	453423	NO
453423	STEVE@GMAIL.COM	453423	NO
343121	DAVETT@GMAIL.COM	343121	NO

I have put in a simple email example, with the solution i'd like to see.

I'm sure it is very easy, but i've had one of those days!!

Any help would be gratefully received.

Richard

[MarqueeCrew]

Hi @rmwillis1973,

One of fuzzy matching's favorite friends is the "Make Group" tool.  I've solved the puzzle for you by following these steps:

1. Find the minimum ParentID associated to an email.
2. Join the minimum ParentID to each record.
3. Make groups of ParentID & Min Parent ID where 1st Key := ParentID & 2nd Key:= Min_ParentID.
4. Join the group results to the incoming data on ParentID == Key
5. Rename GROUP to NEW_Parent_ID

This might be a good test for the inspire16 grand prix.

Cheers,

Mark

[rmwillis1973 · Accepted solution]

Thanks Mark - absolutely perfect solution!

I think i'd have been scratching my head for hours on this solution.