Lesson 4 of 9 · Lab

Label rows from a small lookup table

Lookup copies values from a small reference table onto every row whose key matches. Every row stays, matched or not, so a code missing from the table leaves a blank label and nothing warns you.

Manual Input is a table you type into the flow, for short lists that live in someone's head, like a set of labels.

Marit, who owns fictional Kramerica Bikes, doesn't read bike codes. She dictates five labels: CTY City bike, EBK E-bike, KID Kids' bike, CRG Cargo bike and TDM Tandem. The lower Manual Input has five empty rows for them, and Lookup matches each booking's bike_code.

You use Lookup rather than Join here. Why?

There's one label per code, and every booking must stay, labeled or not. A Join would drop unmatched bookings to its Left output.

Type the labels and find the code nobody listed

  1. In the live Strata flow below, Oplema's browser app for data prep, Lookup and the two blocks after it are red: Lookup has no table yet. Drag from the lower Manual Input to Lookup's lower left dot. Then set Lookup's Lookup Column to code.
  2. Select the lower Manual Input. Click the first empty cell under code, type CTY, press Tab and type City bike.
  3. Fill the next four rows the same way, clicking each row's first cell: EBK E-bike, KID Kids' bike, CRG Cargo bike and TDM Tandem. Then select the block.
  4. Select Summarize, the last block: 26 bookings have an empty bike_type. Scroll Lookup's preview to a row with no label: its bike_code is GRV, which isn't on Marit's list.
  5. Make Formula turn a missing label into Unlisted, so the next unknown code shows up. Check: Summarize shows 26 Unlisted.
    If you're stuck

    Select Formula. In Expression, replace label with COALESCE(label, 'Unlisted'), then select the block. COALESCE uses the first value that isn't empty; it's under Functions › Null handling. Its Output type stays VARCHAR, which is text.

  6. Marit says GRV is the new gravel bike. In the lower Manual Input, hover under the last row and select +. Click the new row's first cell, type GRV, press Tab and type Gravel bike.
  7. Select Summarize. Unlisted has gone, and Gravel bike has 26 bookings.

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 Summarize should show

Before Gravel: City bike 494, E-bike 306, Kids' bike 159, Tandem 155, Cargo bike 78 and Unlisted 26. Lookup's preview keeps all 1,218 rows throughout.

Make the unknown visible

The 26 bookings (€1,239.00 with VAT) are all at Station Road from September 16. The row count didn't change, so nothing warned you. COALESCE turns the next unknown code into Unlisted, which you'll notice in a chart, where a blank would disappear.

Keep the COALESCE after adding Gravel: delete the Gravel row, and the 26 come back as Unlisted.

Your line for Marit: "Every booking has a bike type now. Station Road has been hiring gravel bikes since September 16, 26 bookings so far."

See the Lookup, Manual Input and Formula references. For later: Star schemas shows where labels like these belong.

Common lookup mistakes

  • Counting rows to check a lookup. Lookup never adds or drops rows. Count the blanks instead.
  • Two rows for one code. Lookup takes the first match and ignores the rest. Keep one row per code.
  • Leaving blanks for a chart to hide. A blank label drops out of a chart's legend. Fill it with a word you'll see.

Lookups in SQL and Excel

SELECT b.*, COALESCE(t.label, 'Unlisted') AS bike_type
FROM bookings b
LEFT JOIN bike_types t ON upper(b.bike_code) = upper(t.code);

A left join works as a lookup only with one row per code. Excel's =XLOOKUP(F2, Types!A:A, Types!B:B, "Unlisted") does the same, and its fourth argument plays COALESCE.