Lesson 5 of 9 · Lab

Match misspelled names between two tables

Fuzzy Match pairs rows whose text is similar rather than equal, and writes a score from 0 to 100 for each pair. A threshold decides which pairs count, but a high score only means similar spelling, so you still read the matches.

Fuzzy Match has the same three outputs as Join. Matched holds the pairs that reached the threshold, with their _match_score, and Left and Right hold what found no partner.

Before deciding on bikes for 2027, Marit, who owns fictional Kramerica Bikes, asks what the workshop spent on repairs from July to September, and with whom. Arne's approved list has 8 suppliers, and the workshop types a supplier into each of its 322 repair lines by hand. An exact Join at the top shows the gap.

Will a strict enough threshold separate typos from different businesses?

No. Some different businesses score higher than some real typos. You have to read the pairs.

Match the suppliers and read the scores

  1. This live flow runs in Strata, Oplema's browser app for data prep, and opens on the exact Join. OUTPUT INNER holds 250 of the 322 lines; OUTPUT LEFT holds 72 that don't match exactly. Fuzzy Match and the three blocks after it are red until it has both inputs and a field to match.
  2. Drag from the lower Manual Input, the supplier list, to Fuzzy Match's lower left dot.
  3. Select Fuzzy Match. Under Match Fields, select Add. Set Left Column to supplier_as_typed and Right Column to supplier. Leave Threshold at 85.
  4. OUTPUT MATCHED holds 318 lines; OUTPUT LEFT has 4 from a market stall.
  5. The upper Summarize lists each typed name beside the supplier it matched, with its score.
  6. Read the pairs and decide which typed names are other businesses, then make Filter reject them by name. Check: Filter's OUTPUT FALSE holds 7 lines.
    If you're stuck

    Nordhavn Cycle Hire matched Nordhavn Cycle Care at 95.79, and Larkhill Stationery matched Larkhill Saddlery at 91.21: a hire shop and a stationer. Select Filter, click at the end of its Expression, press Enter and type AND supplier_as_typed NOT IN ('Nordhavn Cycle Hire', 'Larkhill Stationery'). Then select the block.

  7. Select the lower Summarize for spend per approved supplier.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What the lower Summarize should show

311 lines and €9,426.50 across the 8 suppliers, with Tyrewright the largest at €1,418.50. Filter's OUTPUT FALSE holds the 7 rejected lines, €172.00.

A high score means similar spelling

The exact join attributed €7,844.00 of €9,750.50. Reading the pairs removes two businesses that only look like suppliers, and no threshold does that for you: Kettle and Chain, a real typo, scores 90.21. Any threshold above 90.21 loses it, and Nordhavn Cycle Hire survives up to 95.79.

The report's other half is what you wouldn't match: 11 lines, €324.00, to businesses not on the list, the 7 you rejected and 4 market-stall purchases (€152.00).

Your line for Marit: "Tyrewright is the biggest of 8 suppliers this quarter, €1,418.50 of €9,426.50. Another €324.00 went to businesses not on the list."

See the Fuzzy Match reference for the similarity measures. For later: Data quality explains how to measure a problem like this.

Common fuzzy matching mistakes

  • Tuning the threshold until the total looks right. Every threshold trades wrong matches for lost ones. Read the pairs near the line.
  • Reading pairs that matched exactly. A score of 100 is exact. Read the pairs below it, nearest the threshold first.
  • Rejecting by score. Reject a pair because it's a different business, and name it, so the rule still holds next quarter.

Fuzzy matching in SQL and Excel

SELECT r.supplier_as_typed, s.supplier,
       jaro_winkler_similarity(lower(r.supplier_as_typed), lower(s.supplier)) * 100 AS score
FROM repairs r CROSS JOIN suppliers s
QUALIFY row_number() OVER (PARTITION BY r.line_id ORDER BY score DESC) = 1
   AND score >= 85;

This is Fuzzy Match's comparison: every line against every supplier, keeping the best pair. Excel has no fuzzy match; the Fuzzy Lookup add-in leaves the same reading to do.