0

I have a table updating daily with product and supplier information. These fields could vary based on how they are sourced. For example a supplier could be listed as "123 Plc" as well as "123 Timber Plc".

The product "timber" could come in listed against either of these supplier names, as well as "timber2" (again from 1 supplier but could have a slight variation).

I have a reference table of 'clean' data (ie what the product and supplier are finally shown as) that is used to match against the incoming daily info.

I'd like to match each row in the daily update against how much alike it is to the master reference table

I've found code on SO that produced a percentage closeness based on 2 string values and this has been helpful, but only work if I explicitly give it 2 values.

I'd like to run some code that goes through each entry of the daily update and retrieves the primary key of the reference table that has the highest percentage match.

Ref Table:

PK | Product | Supplier        | Concat
1  | Timber  | 123 Timber Plc  | Timber123 Timber Plc

------------------------

Daily update example:

Key | Product | Supplier        | Concat               | Ref PK | Match
1   | Timber  | 123 Timber Plc  | Timber123 Timber Plc | 1      | 100
2   | Timber  | 123 Timber      | Timber123 Timber     | 1      | 85
3   | Timber2 | 123Timber       | Timber2123Timber     | 1      | 65
danverbs
  • 71
  • 1
  • 10
  • Give it two values, but over and over again comparing the percentage and taking the highest. You'll probably need to cross join to pull this off. – JNevill May 01 '19 at 14:17
  • Your requirement is quite close to my [very first question](https://stackoverflow.com/q/5501652/685760) here on SO. I'd also recommend you look at full text indexing to see how that could potentially benefit your scenario. – Mr Moose May 01 '19 at 14:32
  • Thanks I'll take a look :) – danverbs May 01 '19 at 14:56

1 Answers1

0

You can do fuzzy lookups utilizing full text catalogs.

Mike Page
  • 1
  • 1