Hello there, I am trying to validate data across two systems with multiple borrowers and guarantors. Let's say ABC Corp, first guarantor "Ana M" needs to match in the second system (system B) . So, I am checking if that guarantor name is spelled correctly or if it's even included in the second system. The problem is the data isn't in order and I can't guarantee that just sorting the fields will work. Any ideas where I can verify the guarantor's info across systems when the data isn't in the right order? System ABorrower NameGuarantor NameGuarantor AddressABC CorpAna M123 Happy StABC CorpPaula A400 Paula St ABC CorpPeter J100 Peter AvSmile IncPaul J333 Paul AvSmile IncAna N777 Ana StBank Inc Bank J1 Bank Dr System BBorrower NameGuarantor NameGuarantor AddressBank Inc Bank J1 Bank DrABC CorpPaula A400 Paula St ABC CorpPeter J100 Peter AvABC CorpAna M123 Happy StSmile IncAna N777 Ana StSmile IncPaul J333 Paul Av Thank you so much!! Different Systems.png

Join Data and Verify Info Down the Column

Hello there,

I am trying to validate data across two systems with multiple borrowers and guarantors. Let's say ABC Corp, first guarantor "Ana M" needs to match in the second system (system . So, I am checking if that guarantor name is spelled correctly or if it's even included in the second system. The problem is the data isn't in order and I can't guarantee that just sorting the fields will work. Any ideas where I can verify the guarantor's info across systems when the data isn't in the right order?

System A
Borrower Name	Guarantor Name	Guarantor Address
ABC Corp	Ana M	123 Happy St
ABC Corp	Paula A	400 Paula St
ABC Corp	Peter J	100 Peter Av
Smile Inc	Paul J	333 Paul Av
Smile Inc	Ana N	777 Ana St
Bank Inc	Bank J	1 Bank Dr

System B
Borrower Name	Guarantor Name	Guarantor Address
Bank Inc	Bank J	1 Bank Dr
ABC Corp	Paula A	400 Paula St
ABC Corp	Peter J	100 Peter Av
ABC Corp	Ana M	123 Happy St
Smile Inc	Ana N	777 Ana St
Smile Inc	Paul J	333 Paul Av

Thank you so much!!

Different Systems.png

Help

Join

Accepted answers

Prometheus

@Kristie_Pires You can use a Join tool and flag anything that doesn't join. You can also add Fuzzy Matching to the mix for those records that didn't join. You can configure it to output unmatched records along with the match score.

All comments

Prometheus

@Kristie_Pires You can use a Join tool. Just choose which fields you want to join on. Since System A data is the same as System B data, it'll all match up.

Join Sys A to Sys B.PNG

Join Sys A to Sys B.yxmd

Kristie_Pires

@Prometheus Thank you - maybe I oversimplified in my example. In reality, the data isn't as clean, so it won't match up exactly. For example, the Borrower Name on System A is "Paula A" and on System B, it's "Paula AA". I need to be able to look at those two names and say on my validation "Data Do Not Match".

Prometheus

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335