One customer appears as two rows in a grouped report — what does the split compare to decide group identity?
answer
- the report split one thing in two
- printing hides the difference
- identity is equality of the computed value
- normalise before you key, not after
basics
~20 sThe value the key expression produced for each row, compared for equality — not how that value prints. A trailing space, a difference in case, or a timestamp carrying a time of day all produce distinct key values from rows you consider identical.
solid answer
~50 sGroup identity is **equality of the computed key value**, and nothing else. The split evaluates the key expression once per row and puts rows together when those values compare equal under the equality the column's type defines. Two things follow. First, values that *print* the same need not *be* the same: a trailing space, a different case, a padded identifier or a punctuation variant are all distinct values, and the report will show them as distinct rows sitting one above the other. Second, values that you think of as one thing may carry more than you intended — a timestamp that still holds a time of day gives a different key for every second of the same day, and a floating-point value computed two different ways may differ in the last bits. The fix belongs in the key expression: normalise the value there — trim, case-fold, truncate to the unit you actually mean — so every grouped step downstream agrees about identity.
go deeper
Know that two rows go into the same group only when their key values are equal, and that values printing the same are not automatically equal values.
Explain the usual causes — padding, case, a padded identifier, a timestamp still carrying a time of day — and why the reduction is innocent in all of them.
Diagnose by counting distinct keys against expected entities and rendering key values with their extent visible, then correct the key expression rather than the output.
Decide where key normalisation lives for the team, since identity defined differently in two places produces reports that disagree with each other and nothing that flags it.
## Group identity is equality of a computed value The split evaluates the **grouping key** — the column, columns or expression whose value decides which rows belong together — once for every row, and places two rows together exactly when those computed values compare equal. That is the whole rule, and every symptom in this question is a consequence of it. Two things it explicitly does *not* consider: - **How the value looks when displayed.** A report renders the key; the split compared it. Rendering can hide the difference that split the group. - **How similar the rest of the row is.** Rows in one group normally differ in every column but the key, and rows in different groups may be identical everywhere else. ## Text keys that are not equal | what you see in the report | what the split compared | why they are two groups | | --- | --- | --- | | the same name twice | `Acme` and `Acme ` | a trailing space is part of the value | | the same code twice | `ab-100` and `AB-100` | equality on text is normally case-sensitive | | the same identifier twice | `00421` and `421` | one value is text with padding, the other is not the same text | | the same label twice | two visually identical characters from different scripts or with different accents composed differently | the encoded values differ even though the glyphs match | All four print in a way that makes the two rows look like a duplicate of one another, which is why the instinct is to blame the reduction. The reduction is correct; it is computing perfectly over the wrong sets. ## Numeric and time-valued keys - A **timestamp key** that still carries a time of day yields a distinct key for every distinct instant. Grouping "by day" on such a column produces thousands of groups per day, all of which are individually right. - A **floating-point key** computed by two different routes can differ in its last bits while printing identically at the precision a report shows. Keying on a computed floating-point value is fragile for exactly this reason. - A key that is really a **category expressed as a number** — a code stored once with and once without a fractional part, for instance — behaves the same way: one value, two representations, two groups. ## Why the report looks plausible This failure is comfortable to live with, which is why it survives: 1. Both rows are internally consistent — each total is correct for the rows that fed it. 2. The two rows usually sit adjacent, because a sorted split puts near-equal text next to each other, so they read as a formatting quirk. 3. Nothing raises. There is no error condition in "two distinct values produced two groups"; that is the operation working. 4. The grand total across groups is still right, so a reconciliation against the table's total passes. ## Diagnosing it The fastest confirmation that the key expression, not the reduction, is at fault: - Count the distinct key values the split produced and compare that with the number of entities you expect. A higher count means the split manufactured identities. - Render the suspect key values in a way that makes their extent visible — delimited, or with their length beside them — so padding and invisible characters stop hiding. - Check the key column's type. A key you believe is a date but which carries a time, or a value you believe is a number but which arrived as text, explains most of these at a stroke. ## Normalise at the key expression, not at the report The temptation is to merge the two rows after the fact. Resist it: - The report fix has to be reapplied everywhere that key is used, and every other grouped step keeps disagreeing about identity. - It cannot recover anything already folded into the wrong groups — you are patching an output, not correcting the operation. - It hides a data-quality problem that will produce three rows next month instead of two. Normalising in the key expression — trim, case-fold, truncate a timestamp to the unit you mean, bin a computed value deliberately rather than by accident — makes identity explicit, puts it in one place, and makes every downstream grouped operation agree. ## What good sounds like in the room "Two rows means the key expression produced two values. I would count the distinct keys against the number of customers I expect, then look at the key values with their extent visible — padding and case are the usual culprits, and a timestamp carrying a time of day is next. Then I would normalise in the key expression rather than merging the rows in the report, so every other grouped step agrees too."
- How do you confirm quickly that the key expression, not the reduction, is at fault?Count the distinct key values the split produced and compare that with the number of entities you expect. If the group count is higher, the split manufactured extra identities, and the reduction is computing correctly over the wrong sets of rows.
- Why normalise in the key expression rather than merging the offending rows in the report?Because a report-level fix has to be reapplied everywhere that key is used, cannot recover values already folded into the wrong groups, and hides a data problem that will produce three rows next time. Normalising where the key is computed makes every grouped step agree about identity.
- Why is keying on a computed floating-point value risky?Because two routes to the same quantity can differ in their last bits while printing identically at the precision you display. Those values are not equal, so they form separate groups, and the report gives no sign of the difference.
saying these in an interview costs you the question
- Assumes two rows that print the same must land in the same group.
- Blames the reduction when the key expression produced two distinct values.
- Normalises the display of the key rather than the key expression itself.
- Keys on a floating-point value computed two different ways and expects a match.
- Groups on a full timestamp when the intended unit was the calendar day.
- Merges the duplicate rows in the report and calls the problem solved.