Duplicated records from Join tool due to slightly different field between L and R inputs

Question

See attached file.  I joined on the first three fields.  I would have included the 4th field as well, but I would not quite get a match.  The record from source 2 that I DO want to join is the one whose INVC_NUM is more similar to the INVC_NUM from source 1.

I know there's a solution, but I just can't see it.

Joel

test1.xlsx

RobertOdera · Answer

Hi, @oracleoftemple

Are you (ultimately) trying to avoid duplicate counts for invc_ship_qty?

Cheers!

treepruner · Answer

I looked at your spreadsheet and I was unclear if it was a rule that you omit the 1st and last 2 positions to make the match. If it is a rule, just add a Formula tool to create a new field, use the appropriate string functions to select the portion of the field you need and use the new field in a 4 field join.

If the position isn't fixed and you want something close, these might help:

* prior posting in community about matching digits 
* Tool Mastery article on the Fuzzy Match tool

Sarah