Fuzzy Match Reporting

[Todd_Sturner]

I’m puzzled on how to report some fuzzy match data.  I know what I would like to do, but I don’t know what the term is to search for.  I've attached the Excel file of my fuzzy match results (without Name/Address data as I show below)...

What I would like to do is this:

Display the UniqueID Record on one row in Excel, then display the UniqueID2 records, and their match scores, on subsequent rows in Excel, then rinse/repeat for the next UniqueID record.  Essentially:

UniqueID1           Name                   Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

UniqueID1           Name                   Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

UniqueID1           Name                   Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

UniqueID2           Name                   Address                MatchScore        MatchScore_NAME               MatchScore_Address

And so on…

I know I can get the Name/Address data from using joins; however, I want to format the output in the way I'm showing above.

Does anyone have any ideas?

[danilang · Accepted solution]

Hi @Todd_Sturner 

I think this is what you're looking for.

It uses a couple of Multi-Row Formula tools to number the UniqueID1 with a gap of 100 between each and then number each UniqueID2 sequentially within each group.  Use a select tool to rename the UniqueID2 and ID2 fields to UniqueID1 and ID1 and a Summarize tool to get the unique list of UniqueID1s.  Union the 2 datasets and sort on ID1.  

the results look like this with a record for the UniqueID1 value, followed by one record record for each of the matching UniqueID2 rows

Dan

[Todd_Sturner]

Hi, Dan - that worked with a bit of tweaking (as I copied the items into my Alteryx solution).  I found that the ID2 field wasn't changing to match/count with the ID1 field (example: I was getting 101, 102, 103, 104... instead of 101, 102, 201, 202, 203, 301, 401, 402...).  I was able to fix this by adjusting the expression in the second multi-row formula:

if ([Row-1:ID1] = 0) OR ([Row-1:ID1] != ID1) then
ID1+1
else
[Row-1:ID2]+1
endif

After that, it worked!  Thank you so much for the help!!