The Mid-Month Revenue File That Silently Doubles
A financial analyst receives a monthly CSV export from a billing system. The file has 1.2 million rows and columns: InvoiceID, CustomerID, InvoiceDate, Amount, Currency. The export is generated by appending to a rolling 90-day file, so the same invoice can appear more than once if it was reissued. The analyst uses an Input Data tool with default settings, then a Summarize tool grouping on InvoiceID with Sum(Amount). The resulting total is roughly 8% higher than the ledger. When the analyst opens the CSV in a text editor, some rows show Amount wrapped in quotes with a trailing space, e.g. "1,250.00 ". The workflow runs in about 40 seconds and the analyst wants both correctness and speed.
1. Diagnose why the total is inflated and describe how to make the Input Data tool interpret `Amount` correctly, including the trade-off of each configuration choice.
Read the worked answer
Two compounding problems. First, duplicate invoices from the rolling append are being summed twice by the Summarize tool; that alone can inflate totals. Second, the Amount field is not clean: values like "1,250.00 " contain a thousands separator and a trailing space, so depending on the input configuration the field may be read as a string (and then not summed at all) or coerced in a way that truncates or mangles values. Fix the input first: in the Input Data tool, set the field type explicitly to a numeric type (e.g., Double or Fixed Decimal) rather than relying on automatic detection, and confirm the delimiter and quote handling. If the file uses thousands separators inside quotes, a Data Cleansing tool or a Formula with ReplaceChar([Amount], ',', '') followed by Trim() and ToNumber() is safer than trusting auto-detection. Then deduplicate: use a Unique tool keyed on InvoiceID (keeping the latest reissued row by sorting on InvoiceDate descending first), or a Summarize with Group By InvoiceID and Max(InvoiceDate) to pick the surviving record. Trade-offs: forcing types at input is fastest and avoids later conversions, but a single bad row can cause the whole field to be read as string if the type is left to VString; post-input cleansing is more robust but adds a pass over 1.2M rows. Given the 40-second runtime, the extra cleansing step is acceptable.
2. The analyst wants to know whether the duplicates are the whole story or whether the quoted amounts are also contributing. What is a defensible way to isolate the two causes before changing the production workflow?
Read the worked answer
Build a diagnostic branch rather than editing the live workflow. From the Input Data tool, add a Summarize: Count records, Count Distinct InvoiceID, and Count Null on Amount. If Count > Count Distinct, duplicates exist; the difference is the duplicate count. Separately, add a Filter for rows where Amount contains a comma or a leading/trailing space (e.g., Contains([Amount], ',') OR [Amount] != Trim([Amount])). Count those rows and sum their parsed numeric value versus the raw string. Compare three totals: raw sum, deduplicated sum, and deduplicated-plus-cleansed sum. The gap between raw and deduplicated is the duplicate contribution; the gap between deduplicated and cleansed is the parsing contribution. Only after both are quantified should the production workflow be changed, and the change should be validated against the ledger for the same period.