ACAW: Joining Two Weekly Extracts with Mismatched Keys

Joining Two Weekly Extracts with Mismatched Keys

A retail analyst receives two files every Monday. sales.csv has 1.2M rows with a store_code field like 0142, 0143, and 0207 (always 4 digits, zero-padded, stored as text). store_master.xlsx has 380 rows with a Store ID field stored as a number, so 142, 143, 207. The analyst joins on these fields in a Join tool and gets 61% unmatched on the left. They try a Select tool to change the CSV field to a numeric type, but 0142 becomes 142 only sometimes-rows with codes like 0087 become 87, which is fine, yet the join still shows 40% unmatched. A colleague suggests using a Find Replace instead. The analyst needs a reliable join and must keep all 1.2M sales rows, flagging any store with no master record.

1. Diagnose why the type conversion in the Select tool did not fully resolve the join, and propose a robust join strategy that preserves all sales rows.

Read the worked answer

The core issue is that the two keys are semantically the same identifier but encoded differently: text with leading zeros versus a numeric field. Changing the CSV field to a numeric type should make 0142 equal 142, so a 40% residual mismatch means something else is also wrong. Likely causes to check:

Hidden whitespace or non-breaking characters in either key (common with Excel exports). A Data Cleansing tool (trim whitespace, remove nulls) before the join is warranted.
The Select tool conversion may have produced nulls for any code that isn't purely numeric (e.g., a stray 0142 or N/A). Nulls never match, so they inflate the unmatched count.
Store IDs in the master may have decimals (142.0) if the Excel column was inferred as Double; numeric 142.0 equals 142, but string "142.0" does not.

Robust strategy: normalize both sides to the same type and format before joining. On the sales side, use a Formula tool to cast to a clean integer string: ToString(ToNumber(Trim([store_code]))). On the master side, do the same: ToString(ToNumber(Trim([Store ID]))). Join on these derived keys using a Join tool with the left outer behavior (or Join then Union the unmatched-left output back in) so all 1.2M sales rows survive. Add a Formula flag after the join: IF IsNull([Store Name]) THEN "NO MASTER" ELSE "MATCHED" ENDIF. Avoid Find Replace here-it is a lookup, not a relational join, and silently drops or duplicates rows when keys repeat.

2. The analyst wants to verify the join is correct before publishing. What checks would you build, and what would each tell you?

Read the worked answer

Build a small set of assertions rather than eyeballing counts:

Row-count reconciliation: Count rows in sales (1.2M), count matched + unmatched-left from the Join. Matched + unmatched-left must equal 1.2M. If it doesn't, the join is duplicating (many-to-many) or dropping rows.
Uniqueness of the master key: Run a Summarize (Group By Store ID, Count) on the master and filter Count > 1. If any store ID repeats, a sales row can fan out into multiple output rows and inflate the 1.2M. This is the most common silent corruption in joins.
Unmatched profile: Summarize the unmatched-left rows by store_code to see whether the 40% is concentrated in a few codes (a formatting bug) or spread evenly (a genuinely missing master, e.g., new stores).
Spot-check known values: Pick 5 codes you can verify by hand (e.g., 0142 should map to the store named in the master) and confirm the joined name is right, not just non-null.
Null audit: Count nulls in the joined Store Name field and compare to the unmatched count-they should agree.

If the master key is not unique, deduplicate it first (Summarize or Unique tool, choosing a deterministic record) before joining.