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
- 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. - Select the lower Manual Input. Click the first empty cell under
code, typeCTY, press Tab and typeCity bike. - Fill the next four rows the same way, clicking each row's first cell:
EBKE-bike,KIDKids' bike,CRGCargo bike andTDMTandem. Then select the block. - Select Summarize, the last block: 26 bookings have an empty
bike_type. Scroll Lookup's preview to a row with nolabel: itsbike_codeisGRV, which isn't on Marit's list. - Make Formula turn a missing label into
Unlisted, so the next unknown code shows up. Check: Summarize shows 26Unlisted.If you're stuck
Select Formula. In Expression, replace
labelwithCOALESCE(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. - Marit says
GRVis the new gravel bike. In the lower Manual Input, hover under the last row and select +. Click the new row's first cell, typeGRV, press Tab and typeGravel bike. - Select Summarize.
Unlistedhas 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.