Reconciling Two Inventory Lists

The warehouse management system says you have 245 wireless mice in stock. The team just finished a physical count and came back with 242. That's one SKU. You've got 200 more to check. Two spreadsheets, each with a SKU column and a quantity column. You need to know: which items match, which have discrepancies, which exist in the system but weren't counted, and which showed up in the physical count but aren't in the system at all.

This is an inventory reconciliation, and it maps cleanly to a FULL OUTER JOIN. Here's how to do it in ExploreMyData in six steps.

The two files

You have two CSV files. The first is the system export: sku, product_name, system_qty, location. The second is the physical count sheet: sku, physical_qty, counted_by. The SKU column is the common key.

The tricky part: items might exist in one file but not the other. The system could have SKUs that weren't physically counted (maybe the team skipped a shelf). The physical count might include items that aren't in the system (phantom stock, or new arrivals that weren't entered yet). A regular INNER JOIN would silently drop both of these categories. You need a FULL OUTER JOIN.

Step 1: Load both files

Open the first file (the system export) in ExploreMyData. Then open the second file (the physical count). Both appear in the file panel on the left. You'll work from the system export as your primary table.

Step 2: FULL OUTER JOIN on SKU

Select Join from the Data group. Set the join type to FULL OUTER. Choose sku as the join column on both sides.

A FULL OUTER JOIN keeps every row from both tables. If a SKU exists in both, the row has values from both. If a SKU only exists in the system export, the physical count columns are NULL. If a SKU only exists in the physical count, the system columns are NULL. This is exactly what you want for reconciliation.

After the join, you'll see columns from both files. The result might look like sku, product_name, system_qty, location, physical_qty, counted_by.

Step 3: Calculate the difference

Select Add Column. Name the new column difference and enter:

COALESCE(system_qty, 0) - COALESCE(physical_qty, 0)

The COALESCE handles NULLs from the outer join. If an item is only in the system (physical_qty is NULL), the difference equals the full system quantity. If it's only in the physical count, the difference is negative. A positive number means the system thinks you have more than you actually do. Negative means you found stock that the system doesn't know about.

Step 4: Add a status flag

Add another column called status:

CASE WHEN system_qty IS NULL THEN 'not_in_system' WHEN physical_qty IS NULL THEN 'not_counted' WHEN difference = 0 THEN 'match' WHEN difference > 0 THEN 'surplus_in_system' ELSE 'shortage_in_system' END

Now every row has a plain-English status. Five possible values:

• match - system and physical counts agree
• surplus_in_system - system says more than physical reality
• shortage_in_system - physical count is higher than system
• not_counted - item exists in system but wasn't physically counted
• not_in_system - physically present but not in the system

Step 5: Filter to mismatches

Select Filter and set the condition: status != 'match'.

This strips out all the items where the counts agree and shows only the problems. If you had 200 SKUs and 170 matched, you're now looking at 30 rows. Each one tells you exactly what's wrong: the SKU, the system quantity, the physical quantity, the difference, and the status.

Step 6: Clean up the output

Select Select Columns from the Columns group. Pick the columns that matter for the report: sku, product_name, system_qty, physical_qty, difference, status, location. Drop the counted_by column unless you need it for the audit trail.

Export the result as a CSV. That's your reconciliation report: every discrepancy, categorized and quantified, ready for the warehouse team to investigate.

The full pipeline

1. Load both inventory files
2. Join (FULL OUTER on sku)
3. Add Column (difference: system_qty - physical_qty)
4. Add Column (status: match / surplus / shortage / not_counted / not_in_system)
5. Filter (status != 'match')
6. Select Columns (clean output for the report)

Six steps. The whole thing takes a couple of minutes to set up, and the join runs in milliseconds thanks to DuckDB. When the next physical count comes in, just load the new file and the pipeline runs again on the fresh data.

The key insight is the FULL OUTER JOIN. An INNER JOIN would silently hide items that only exist in one file, which is exactly the kind of problem reconciliation is supposed to catch.

Reconcile your inventory files →