Lesson 9 of 15 · Lab
Filtering rows: WHERE, conditions and nulls
A filter keeps the rows that meet a condition and drops the rest. In SQL, that's a WHERE clause. A null is neither true nor false, so a row with an unknown value fails every ordinary comparison, including "not equal".
Take three rows with statuses booked, pending and null. In SQL, status <> 'pending' returns one row, not two.
You'll write the rule for fictional Vandelay Coffee Supply, whose 16 invoices now have one row each. It wants booked invoices dated in Q3.
This rule starts its date range on June 1. INV-1016 has no date at all. Which output should it land in?
False. An empty date can't be between two dates, so the row fails the condition. A date rule always carries this decision about unknowns.
Fix one date in a filter rule
- This live flow in Strata, Oplema's browser app for data prep, opens with Filter selected and its rule in Expression. OUTPUT TRUE opens first.
- Select Summarize, the last block. It adds up the kept rows: 14 rows, 13,110. Q3 can't include June, though.
- Select Filter. In Expression, double-click
06in2026-06-01and type07. Then select the Filter block. - Read OUTPUT TRUE. INV-1001 has gone.
- Open OUTPUT FALSE. Every row here should have a reason you can name.
- Select Summarize again.
rowsandtotalshould both be lower.
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 two outputs hold
True keeps 13 rows totaling €11,910. False holds 3: INV-1001 from June, INV-1009 canceled, INV-1016 pending with no date. 13 + 3 = 16.
Rejected rows are your reconciliation
Most tools throw away the False output. Here it's your reconciliation, because kept plus rejected has to equal what came in. Every rejected row should also have a reason you can say aloud. A row you can't explain means your rule is wider than your question.
The expression compares true dates, which works because they were parsed from text first. See the Filter reference. A filter on a table changes what later steps receive, while a dashboard filter changes what one reader sees.
The rule removes €1,300 of pending invoices and €1,200 from June, taking the sales report's list value from €14,410 to €11,910, on 13 rows. That's still €1,742 above the finance report, and joining tables by key shows why.
Common filter mistakes
- Forgetting nulls. To keep them, say so with
OR status IS NULL.= NULLnever matches, so useIS NULL. - Mixing
ANDandORwithout parentheses.ANDbinds first, soa OR b AND cmeansa OR (b AND c). - Using
BETWEENon timestamps. An end date of 2026-09-30 means midnight, so it misses the rest of that day. - Matching text exactly on messy values. Clean them first.
WHERE and Excel's FILTER
SELECT * FROM invoices
WHERE status = 'booked'
AND invoice_on BETWEEN DATE '2026-07-01' AND DATE '2026-09-30'SQL Server has no DATE '...' literal, so write '2026-07-01' there.
In Excel, =FILTER(A2:C100, B2:B100="booked") returns the matching rows, and AutoFilter hides the others. Excel differs on nulls, because a blank cell counts as different from "pending", so B2<>"pending" keeps blanks.