Lesson 9 of 9 · Lab

Debug a flow that runs but gives the wrong total

A flow with no red blocks can still be wrong. To find out, compare what each block passes on with a figure you trust, starting where the numbers first disagree.

Marit, who owns fictional Kramerica Bikes, has your eight findings and wants one month-end table for her three shops.

Noor, at Station Road, built a month-end flow from the three till files. It runs without errors and gives 1,052 finished hires and €38,580.80 net, but you know there are 1,096.

Noor's total is 44 hires short. Could more than one block be wrong?

Yes. One adds hires and another removes more, so the gap is smaller than either.

Fix Noor's flow

  1. Noor's flow is live in Strata, Oplema's browser app for data prep, with Formula, near the end, selected: Harbour 311 hires, Station Road 376, Old Town 365. Old Town's use_per_day of 1.02 is impossible: no shop hires out more bikes than it owns.
  2. Harbour should have 428 finished hires, Station Road 376 and Old Town 292. Select blocks from the left until a count first disagrees, and fix each fault.
    If you're stuck

    Unique should leave 1,218 rows, but its OUTPUT DUPLICATE is empty: Match On has booking_id and Source. Clear Source.

    Filter's rule is status = 'completed', and further down its OUTPUT FALSE are Harbour's finished hires from September 23, which say returned. Change the Expression to status IN ('completed', 'returned'), then select the block.

  3. Two things Marit asked for, without help: from Filter's OUTPUT TRUE, add a branch that counts hires for each extra (an add-on such as a helmet or lock). Split the packed extras cells into rows first.
  4. Wire the budget, the unwired Manual Input, to a Transpose with shop and line as Key and only Sep as Data, and read the Hires rows. The Role ▾ menu above the list sets every row at once: choose Skip, then set the three you need.
  5. Select Run local. Output downloads the month-end table as a CSV file.

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 month-end table should show
Month-end, September
ShopHiresBikesNetNet per bike ownedBikes out per owned bike per open day
Harbour428654€12,938.00€3810.64
Station Road376728€21,168.80€4600.52
Old Town292347€6,163.20€3850.83

1,096 hires and €40,270.00 net. 479 hires took a helmet. September's hires budget is €41,500.00: Harbour €14,000.00, Station Road €21,500.00 and Old Town €6,000.00.

Build month-end from the files

Now build the same table from the three till files and Marit's fleet table of bikes owned and open days per shop. Stack the files so a rename stops the flow, and keep each booking once and every finished hire. Then total each shop's hires, bikes and takings. Attach the fleet table by shop with Join. Both tables have shop, so Join keeps one column. Then work out net in cents, net per bike owned in whole euros and bikes out per owned bike per open day to two decimals, from dated hires only. Drop new blocks on empty canvas: one dropped near a block can wire itself to it. Use the palette's Search, the magnifier, to find a block. Red text names the fault: a misspelled column, files whose columns differ or a block with nothing set yet.

If you're stuck: the settings
  1. Rename Harbour's price to amount with Select, then stack the three files with Union, set to Error on mismatch.
  2. Cleanse: trim whitespace and set status to lower case.
  3. Number: read amount the European way into amount_eur.
  4. Unique on booking_id alone, then Filter on status IN ('completed', 'returned').
  5. Formula: dated_bikes is CASE WHEN hire_date = 'see note' THEN 0 ELSE bikes END, Output type BIGINT.
  6. Summarize by shop: a Count named hires, and a Sum each of bikes, dated_bikes and amount_eur, named bikes, dated_bikes and gross. Each Output comes filled in, such as count or sum: select all (Ctrl+A or Cmd+A) and type its name, because the Formulas below use them.
  7. Join the fleet table on shop.
  8. Formula, one row each: net_eur is gross * 0.8, Output type DECIMAL; net_per_bike is round(gross * 0.8 / bikes_owned), BIGINT; use_per_day is round(dated_bikes / bikes_owned / open_days, 2), DOUBLE.

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).

Check your table

It should match Noor's fixed table exactly. The table above shows six of its ten columns. If Old Town shows 365 hires, check Unique; if Harbour shows 311, check the Filter.

Two faults, one plausible number

So which shop is short of bikes? Takings say Station Road; use per bike says Old Town.

Noor's Unique kept the repeated week, 73 extra hires and €1,528.00 net, and her Filter missed Harbour's returned, 117 hires and €3,217.20. Seven hires dated see note, 3 at Harbour and 4 at the other shops (€249.00 with VAT), count in the month and on no day.

Station Road earns most, but per bike it owns, Old Town works hardest: 0.83 bikes out per owned bike per open day, against 0.64 at Harbour and 0.52 at Station Road, from dated hires only. A monthly average can't show whether Old Town runs out on Saturdays, and the till records hires, not people turned away.

Your line for Marit: "Station Road earns most, at €21,168.80 net. Old Town works its bikes hardest."

Marit replies: "And I can't ask you every time." The next course builds a daily view she can read herself and someone else can rerun.

For later: How tables change shows how to read row counts through a flow.

Common debugging mistakes

  • Checking only the last block. A plausible total can hide faults that cancel out.
  • Fixing the first fault and stopping. After the Unique fix the total moved the wrong way, because the Filter fault remained.
  • Assuming a colleague's settings are deliberate. Source in a key is rarely meant.

Debugging in SQL and Excel

SELECT 'stacked' AS step, count(*) FROM stacked
UNION ALL SELECT 'unique', count(DISTINCT booking_id) FROM stacked
UNION ALL SELECT 'finished', count(*) FROM deduped
  WHERE status IN ('completed', 'returned');

A count after each step is the SQL version of selecting each block in turn.

Course complete Now use your own file.

A month-end table for fictional Kramerica Bikes' three shops, with every gap explained and built mostly unaided.