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)StatusNameOther NameIDUnitCodeActiveJohn SmithJohn Smith LDAActiveSarah JaneSarah Robertson LDACompletePaul CrowPaul Crow LGHCActiveBecca TorriniBecca Torrini HYTPendingYT SmithsonYT Smithson HYTCompleteGeorgio YanGeorgio Yan LGHC I would like to fuzzy match against a reference file Reference File (Unchanged)ActivityNameIDUnitCodeAddSmith, John A.01234LDAAddRobertson, Sarah768LDAOutSmithson, Yan T.00678HYTInTorini, Rebecca892HYTOutYan, Georgio00765LGHCOutCrow, Paul00214LGHC 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)StatusNameIDUnitCodeCompleteSmith, John A.01234LDACompleteRobertson, Sarah00768LDACompletePaul Crow LGHCCompleteTorini, Rebecca00892HYTPendingYT Smithson HYTCompleteGeorgio 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! FuzzyAA.yxmd

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

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!

FuzzyAA.yxmd

Workflow

Help

Fuzzy Match

Accepted answers

atcodedog05

Hi @Wixard

I have tweaked @ArtApa workflow.

Workflow:

Hope this helps : )

FuzzyAA.yxmd

All comments

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 : )

Fuzzy Match.yxmd

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

Hi @Wixard

I have tweaked @ArtApa workflow.

Workflow:

Hope this helps : )

FuzzyAA.yxmd

Wixard

@atcodedog05 this is awesome! Thank you so much!

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167