Using Fuzzy Logic to compare 2 columns against a single reference column

[Wixard]

Hi all,

Was wondering if there was a way of comparing two columns against a single reference column?

For example

I have a Working File that contains names across two columns (Name, Other Name - for example maiden name changes)

Working File (Initial)
Status	Name	Other Name	ID	UnitCode
Active	John Smith	John Smith		LDA
Active	Sarah Jane	Sarah Robertson		LDA
Complete	Paul Crow	Paul Crow		LGHC
Active	Becca Torrini	Becca Torrini		HYT
Pending	YT Smithson	YT Smithson		HYT
Complete	Georgio Yan	Georgio Yan		LGHC

I would like to fuzzy match against a reference file

Reference File (Unchanged)
Activity	Name	ID	UnitCode
Add	Smith, John A.	01234	LDA
Add	Robertson, Sarah	768	LDA
Out	Smithson, Yan T.	00678	HYT
In	Torini, Rebecca	892	HYT
Out	Yan, Georgio	00765	LGHC
Out	Crow, Paul	00214	LGHC

Criteria: If either Name or Other Name from the working file matches closely to the Name in the Reference file, it will bring back a match.

Updated working file:

Working File (Updated)
Status	Name	ID	UnitCode
Complete	Smith, John A.	01234	LDA
Complete	Robertson, Sarah	00768	LDA
Complete	Paul Crow		LGHC
Complete	Torini, Rebecca	00892	HYT
Pending	YT Smithson		HYT
Complete	Georgio Yan		LGHC

I was looking for a way to just tweak @ArtApa brilliant solution to the below of:

1. bringing back the Name from the reference file

2. stamping the ID to the Working file

3. Marking Status as Complete.

Solved: Re: Lookup using fuzzy logic to stamp data from re... - Alteryx Community

The only change is to match across two columns instead of just 1.

Thank you!

[DawnDuong]

Hi @Wixard 

One way to tweak this is:

1) Put a record ID to your working file

2) "Stack" your "name" and "other name" column on top of each other. this can be done by splitting into 2 flows, each having a select tool, and then union them again.

3) run your fuzzy match 

This way, you are back to having 1 working column vs. 1 reference column. Later on you can use the record ID in step 1 to identify rows which 0, 1, or 2 possible fuzzy matches.
Dawn.

[atcodedog05]

Hi @Wixard 

Here is how you can do it.

1. I am first finding the match from working file to be renamed in reference file.

2. I am concating name and other name from working file to make it easier for the matching the rows.

3. Matching using find and replace tool by checking whether the name from reference file is present in working file name pair(Name and Other Name).

Hope this helps : )

[Wixard]

Thank you @atcodedog05  and @DawnDuong !

Two great solutions - am hoping to implement one that requires the least amount of modification, since I used @ArtApa FuzzyMatchAA workflow to build a very large canvas, I was hoping that I could tweak that to make this work.

@DawnDuong RecordIDs are already present in the attached workflow, so the stacking method seems like it may be a bit easier to implement in my workflow. I'm assuming you mean split the dataset and stack on top of each other, so you have duplicate records, one for Name and the other for Other Name? How do I split like that? Sorry still a bit new to Alteryx.

[atcodedog05 · Accepted solution]

Hi @Wixard 

I have tweaked @ArtApa workflow.

Workflow:

Hope this helps : )

[Wixard]

@atcodedog05  this is awesome! Thank you so much!