Help comparing columns in two documents

Help Studio
, , ,
1

Hello,

I have two spreadsheets containing ID numbers where people have submitted their SSN details (dt1 and dt2).
I need to report the percentage simularities between SSN_Number values by using a third spreadsheet, dt3.
Dt3 should show a summary row containing a count of potential matches and highest percentage match value.

What is the best approach to this?

dt1
ID, SSN_Number
123, 987654
456, 789456
789, 65432

dt2
SSN_Number
978645
878645
784592
789456
654321
6698

dt3
ID, Count of Potential matches > 50%, Highest percentage match

2

hi @qwerty1

You can loop through both the DT and get row to row comparison:

Use
UipathTeam.String.Activities
There we have one activity Levenshtein Algorithm, which will return you a Decimal value.

Thanks
Happy Automation! :smiley:

3

Thank you. Do you have a sample xaml that you could share?

4

Hi @qwerty1 ,
Help us understand it by providing the dt3 output for the given inputs.

Happy Automation!
Manoj Vijayakumar

5

don’t have a sample xaml, but An Easier Way

Join the two data tables based on ID column> outputDT
After performing join, use add Data column “Matchperc”
loop on Output DT>

assign it to percentageMatch.
assign currentRow(“Matchperc”)=percentageMatch

exit loop

assign ResultsDT= (From d In DT.AsEnumerable
Group d By d("ID").ToString.Trim Into grp=Group
Select R=grp.OrderBy(Function (r) r("Matchperc")).First()).CopyToDataTable

Here ResultsDT and OutputDT are of type DataTable.

Thanks

Happy Automation! :smiley:

6

Thank you for explaining. If dt2 doesn’t have the IDs is there another way to do this?
I’ve just been made aware that the dataset has just been updated. I’ve just updated the post to reflect.

7

Then you would have to do Nested loops
Build Data Table: ID,SSN_Number, SSN_noDt2, MAtchperc> Output to DT3
Parent loop on DT1:
Child loop on DT2,
Create a 3rd DT and
add dataRow to Dt3 with all 4 values after calculating match percentage the same way.

Thanks