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 returned line 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/a is 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.

August's published answers, from pendant-sales-aug.csv
CheckAugust
Rows in384
Resent duplicates removed14
Void rows9
No-price rows6
Rows counted355
Quayside net€3,844.35
Marketplace net€2,852.30
Net total€6,696.65
Top categoryFiction, €1,799.40
Unknown title€123.50
Highest dateAugust 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.

September's answers, with no edits to the flow
CheckSeptember
Rows in332
Resent duplicates removed12
Void rows16
No-price rows5
Rows counted299
Quayside net€2,181.30
Marketplace net€3,058.65
Net total€5,239.95
Top categoryFiction, €1,405.30
Unknown title€120.40
Highest dateSeptember 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, RETURNED and returned with 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.