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!
Solved! Go to Solution.
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.
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 : )
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 this is awesome! Thank you so much!