Lesson 3 of 10 · Lab
Choose columns and set data types in a flow
Select keeps the columns you check and drops the rest. Its Type column shows what each column holds, and it can cast a column to another type. A cast only converts values already written the way that type expects.
Dropping the columns you don't need makes every later preview easier to read. Tomas's file doesn't change, so nothing is lost.
Marit's weekly report at fictional Kramerica Bikes needs six of the twelve columns in Harbour's export: booking_id, hire_date, bike_code, bikes, amount and status. This live flow in Strata, Oplema's browser app for data prep, holds the same 321 rows in Manual Input, a block that holds a table inside the flow in place of Input. Summarize, on the right, is a check that counts what reaches it.
bikes is a number and amount is text. Will casting amount to DOUBLE give you amounts you can add up?
No. You get a number column with nothing in it. A cast reads a period as the decimal point, so 24,50 and gratis both become NULL.
Keep six columns and read their types
- Select is selected. Under Columns, each of the 12 columns has its checkbox selected, and Type shows what it holds:
bikesis BIGINT, a whole number, and the rest are VARCHAR, text. Hover over a cut-short name to read it. - Clear the checkbox for
shop,customer_id,channel,extras,deskandtill_note. The preview now has six columns. - Drag from Select's right dot to Summarize's left dot. Drop on the dot, not the block.
- Select Summarize.
rowsreads 321,bikesadds up to 500 andamountscounts 321 amounts that aren't empty. - Select the Select block. Open the Type dropdown for
amountand choose DOUBLE. - Select Summarize.
amountsnow reads 0. Every amount became NULL, and nothing turned red. - Select the Select block and set
amount's Type back to its first option, VARCHAR.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
What you should see
Six columns, from booking_id to status. With amount as text, Summarize reads 321 rows, 500 bikes and 321 amounts. Cast to DOUBLE, the amounts drop to 0.
A cast can succeed and still lose every value
The cast did what it promises: a value that can't convert becomes NULL. That's how a column typed as a number can add up to nothing. Leave amount as text for now. Two lessons on, you read it the European way.
bikes needed no cast, because the file already writes whole numbers. The columns you dropped are still in Tomas's file if a later question needs them.
See the Select reference. For later: Data types and casting explains what a cast does and doesn't do.
Common column mistakes
- Casting text written for people.
24,50and1.001,00only become numbers through a parse that knows their format. - Reading NULL as zero. A failed cast leaves NULL, and a sum skips it, so a total comes out low with no error.
- Dropping the ID. Keep
booking_ideven though the report doesn't show it. It's how you trace a figure back to a row.
SELECT and TRY_CAST in SQL and Excel
SELECT booking_id, hire_date, bike_code, bikes, amount, status
FROM harbour;
SELECT TRY_CAST(amount AS DOUBLE) FROM harbour; -- NULL for 24,50Naming columns in SELECT is what Select does. DuckDB's TRY_CAST returns NULL where a value can't convert, as Select's Type does, while CAST stops with an error. In Excel you delete the columns, and =VALUE("24,50") fails on a computer that uses a period for decimals.