Lesson 7 of 8 · Lab
Practice data project: raw files to a report
A flow is finished when it gives the right answer on a file it has never seen. Build this one from the brief alone, check it against August, then run it on September, changing only the file the sales Input reads.
This project runs in Strata, Oplema's browser app for data prep, rather than in this page, so you need a desktop browser and the free account you made in Move a flow's files to Cloud. You start on a blank canvas. There are no steps: everything it needs, you've done in these three courses.
Fictional Pendant Books is two independent bookshops, Quayside and Marketplace. Each month the till exports one file of sales. The owners want three answers for the month, published as a dashboard: which shop made the most net sales, which category sold most and on which date net sales were highest.
The brief
Upload pendant-sales-aug.csv and pendant-titles.csv to a Cloud folder. The titles file gives each title_code its title and category.
Net, in this brief, means:
- Each sale once. The till sometimes sends a sale twice with the same
sale_id. - Quantity times price.
qty × unit_price. Prices use a decimal comma. - Returns count against the total. A
returnedline is negative. Status spellings vary. - Void lines don't count. Leave them out and report how many there were.
- No price, no sale. A line priced
n/ais left out and reported. - An unknown title still counts. It stays in the total, under the category Unknown.
- One sale has no date. It's dated
n/a. It counts in the shop, category and month totals, on no date, and you report it.
VAT doesn't come into it. Deliver a flow, an Output file in Cloud, a dashboard with a chart, a saved version and a published link. Every row you leave out must be attributed to the block output it left by.
Hints, if you're stuck
Dates are written 2026/08/05, the Year first layout in DateTime. A Formula with CASE WHEN makes returns negative, and COALESCE names the unknown category. Count the Unique and Filter outputs you don't use: those counts are your report. Set the net column's Output type to DECIMAL with 2 decimal places, as in Add a calculated column to a table, so totals add up in exact cents.
| Check | August |
|---|---|
| Rows in | 384 |
| Resent duplicates removed | 14 |
| Void rows | 9 |
| No-price rows | 6 |
| Rows counted | 355 |
| Quayside net | €3,844.35 |
| Marketplace net | €2,852.30 |
| Net total | €6,696.65 |
| Top category | Fiction, €1,799.40 |
| Unknown title | €123.50 |
| Highest date | August 27, €328.70 |
| Sale with no date | €26.00 |
September, without edits
Upload pendant-sales-sep.csv, pick it in your sales Input and select Run local. In Vista, select Publish, then Update publication. Change nothing else.
| Check | September |
|---|---|
| Rows in | 332 |
| Resent duplicates removed | 12 |
| Void rows | 16 |
| No-price rows | 5 |
| Rows counted | 299 |
| Quayside net | €2,181.30 |
| Marketplace net | €3,058.65 |
| Net total | €5,239.95 |
| Top category | Fiction, €1,405.30 |
| Unknown title | €120.40 |
| Highest date | September 14, €321.30 |
| Sale with no date | €14.90 |
The winner flips: Marketplace leads in September. A flow that hardcodes a shop, a date or a row count shows it here, and a correct flow needs no edits. If both months match, you've built a report from raw files, unaided, and proved it on a second file.
Common build mistakes
- Removing duplicates after filtering. The void count then includes a resent copy: September reports 17 void rows and 11 duplicates instead of 16 and 12. Keep each sale once first.
- Matching status as typed.
Returned,RETURNEDandreturnedwith a leading space are one status. Clean the text before any rule reads it. - Dropping the unknown title. A Join's Inner output loses it. Lookup keeps every sale and leaves the category empty to fill.
What nobody decided for you
Every rule in these courses was handed to you, or one person decided it. Marit defined short. The brief defined net. What happens when nobody agrees what a number means, or two reports of the same thing disagree?
That's where Data preparation and Business intelligence start. They follow a fictional coffee supplier and stand on their own. The last page of this course lists the tools you haven't needed yet.