Skip to content
Docs
Open Mammoth
Learn › Clean and shape data › Fix a failing step: open the docs menu

Fix a join that returns zero or too many rows

A Join that returns zero rows almost always has key values that differ between the two datasets. One that returns too many rows has repeating keys in the joined View. Both are fixed with a cleaning step before the join, on the Join task's inputs. For setting up a join, see Join two datasets on a shared column.

Fix a join that returns zero rows

Keys fail to match when they have:

  • different wording, such as US and United States;
  • trailing spaces: New York doesn't match New York;
  • different case: APPLE and apple may not match;
  • different types, such as text joined to a number.

To diagnose, check the Join type first. An Inner join drops current View rows with no match; a Left join keeps them with empty joined columns. Then use Preview Data at this Task to compare a sample of keys on both sides.

ProblemFix, on both sides before the join
SpacesText Formatting with Normalize whitespace
CaseText Formatting with Change text case
Different wordingFind & Replace to standardise values
Different typesConvert Column Type

Fix rows lost to empty join keys

Empty join keys lose rows too: the join completes, the row count drops and joined columns are empty. Rows with no Customer_ID can't match, and the Data Quality report shows a high share of empty keys.

  1. Add a Conditional Filter to separate rows with empty keys.
  2. Create a View for the records that need attention.
  3. Set up a Messaging automation to alert your data quality team.

Check key completeness before you build complex pipelines.

Fix a join that returns too many rows

Mammoth warns Key column(s) have non-unique values when the joined View's keys repeat, because each row then matches several rows. A Join that would produce more than 100,000 rows and more than five times the larger input is rejected. The task shows Runtime error. Contact support for help., and the full message says the row count would rise to a very large value.

Failing Join task outlined in red showing the runtime error and the Key column(s) have non-unique values warning with a More details link, above Delete and Fix error buttons
  • Switch to Lookup if you need one value per row.
  • Remove duplicates from the other dataset first, with Remove Duplicate Rows on the key column.
  • Summarise the other dataset with Group & Pivot before joining.
Was this page helpful?

Something wrong? support@mammoth.io