Lesson 2 of 9 · Lab

Remove rows that appear in two files

Unique keeps the first row for each key and sends later matches to its Duplicate output. The result depends on what you call a match: too many key columns and nothing is a duplicate.

By default Unique compares every column. Rows that are the same booking but differ in one column, such as Source, aren't duplicates by that rule.

Old Town's till at fictional Kramerica Bikes failed from September 14 to 20, so Leila, who manages Old Town, had that week's bookings keyed at Station Road. When the till came back, her staff keyed them again at Old Town. Both files hold the same bookings, with the same booking_id. The stack from the last lesson is here, with a Unique beside it that compares every column.

Unique starts by comparing every column. How many duplicates will it find?

None. The repeated rows match in every column but Source, which says which file each copy came from.

Find the week that's there twice

  1. Unique is selected and red in this live Strata flow, Oplema's browser app for data prep, because it has no input yet. Drag from Union to Unique: OUTPUT UNIQUE holds all 1,298 rows, and OUTPUT DUPLICATE is empty.
  2. Under Match On, keep only what identifies a booking, so the repeated rows go to OUTPUT DUPLICATE. Check: OUTPUT DUPLICATE holds 80 rows.
    If you're stuck

    Clear the top box, so no column is selected, then select booking_id alone. With Source selected as well, nothing is a duplicate, because each copy has a different Source.

  3. Read OUTPUT DUPLICATE: all 80 are from Old Town, dated September 15 to 20, as Old Town is closed on Mondays. Scroll right: every one says input_3.
  4. Open OUTPUT UNIQUE: 1,218 rows.

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

What each output holds

Unique holds 1,218 bookings and Duplicate 80, worth €2,054.50 with VAT. 1,218 + 80 = 1,298. Of the 80, 73 are finished hires with 82 bikes between them.

Name the rows you removed

Unique keeps the first copy of each booking. Union passes rows on in wire order, so Station Road's copies, input_2, come first and stay. Old Town's own copies, input_3, are the ones removed. Both copies say Old Town in shop, so no figure moves, but Leila can see exactly which of her rows went.

Left in, the week counts 82 of Old Town's bikes twice. Its September bikes out would read 429 instead of 347, and the smallest shop would look short of bikes for the wrong reason.

Your line for Marit: "1,218 bookings. 80 rows were Old Town's bad week, counted twice."

See the Unique reference. For later: Finding and removing duplicate rows explains how to decide which copy to keep.

Common duplicate mistakes

  • Keying on every column. A copy that differs in one column, such as a file name or an export date, survives. Key on what identifies the thing.
  • Keying on too little. Two different bookings can share a date and an amount. Use the ID if there is one.
  • Throwing the duplicates away unread. Keep the Duplicate output where you can count it, so a removal can be explained.

Rows in two files: SQL and Excel

SELECT * FROM stacked
QUALIFY row_number() OVER (PARTITION BY booking_id ORDER BY
  CASE source WHEN 'harbour' THEN 1 WHEN 'station' THEN 2 ELSE 3 END) = 1;

The ORDER BY decides which copy is first, as wire order does here. Ordering by the source's name would keep Old Town's copies instead, because old_town sorts before station. SELECT DISTINCT is the every-column rule and would remove nothing. In Excel, Remove Duplicates asks which columns to compare; select only booking_id, and copy the sheet first, because the removed rows are gone.