3 Way Match - Find Best Match - OCR invoice line level detail & Purchase Order Receipt Data

[DeanBek]

Great Product and Excellent work so far!

Curious about your thoughts of using this tool to perform a 3-way match (invoice price vs purchase order price - quantity received into inventory vs invoice quantity)?

OCR is a great tool to help with data entry in accounts payable, but it is mainly used to get Invoice Header (Invoice No, Inv Date, Inv Amount, Supplier) but has challenges doing a three way match with line level detail. Imagine having 300 line invoice and manually combing though the lines to find the pricing errors and shortage on these two lines.

Purchase Receipt Data

Product	Desc	PO Price	PO Qty	Expect Price
MC142	CBL MTL CLAD 68570929223 100' 14-2 600V ALUM	105	12	$1,260
MC145	CBL MTL CLAD 68590928223 100' 14-5 600V ALUM	115	10	$1,150

OCR Invoice Line Level Detail

Product	Desc	Inv Price	Inv Qty	Actual Price
68570929223	MC-142 CBL MTL CLAD 100' 14-2 600V ALUM	107	12	$1,284
68590928223	MC-145 CBL MTL CLAD 100' 14-5 600V ALUM	117	9	$1,053

Even thought only one column of data matches (12 quantity) on line 1 and zero columns match on line 2, if the formatting was better a human would be able to readily identify $24 pricing problem on the first line and the $18 pricing difference on the second line with a short shipment of 1. However, these could be line 37 and 92 on the receiver and line 188 and 78 on the invoice and becomes a nightmare to find.

The goal would be to find the best match between the 300 line items on the receiver and the 300 lines on the invoice. Is it beyond the scope of this tool to help solve this problem?

Thank you in advance for responding!

[NickCrews]

If I understand you correctly (link a table of 300 records with another table of 300 records, where each record in A will match exactly one record in B), then this exactly what splink is designed for. You will have to write a bit of custom SQL to implement the comparisons that you are talking about, but it would work.

For the scale that you are talking about, there are only 300x300=90_000 possible comparisons, so honestly I think it might be easier to just write this logic in pure python and skip splink entirely.

[RobinL]

Agreed - if you have a clear decision boundary it might be simpler to write it in Python.

If you did want to use Splink the closest example we have of what you're trying to do is here:
https://moj-analytical-services.github.io/splink/demos/example_transactions.html