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 seven 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

Click the green + in the Pipeline panel and select Join from the Data group. In section 1, pick inventory_physical.csv as the table and Full as the join type. In section 2, set both the left key and the right key to sku.

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.

The third section of the panel is "Columns to include from right table (leave empty for all)". Leave it empty for now. That means the join selects everything from both sides, and here is the part that surprises people: the key column comes back twice. The right table also has a column called sku, so the pipeline view renames the second copy with a _1 suffix. You end up with sku, product_name, system_qty, location, sku_1, physical_qty and counted_by.

In an inner join nobody notices, because the two copies always hold the same value. In a FULL OUTER JOIN they do not. A row that only exists in the system export has its SKU in sku and NULL in sku_1. A row that only exists in the physical count is the other way round: NULL in sku, the actual SKU in sku_1. Neither column on its own identifies every row.

Step 3: Rebuild one usable SKU column

Select Add Column from the Columns group. Name it sku_final and give it this expression:

COALESCE("sku", "sku_1")

COALESCE returns the first value that is not NULL, so every row now carries its SKU in one place: matched rows and system-only rows take it from sku, count-sheet-only rows take it from sku_1. Use sku_final from here on and treat the two originals as plumbing.

There is a shorter route: go back to the join and use "Columns to include from right table" to pick physical_qty and counted_by only, leaving the right key out. One less step, one less column, and no duplicate. The catch is that rows found only in the physical count then have no SKU at all, because the only copy of their key was the one you excluded. For most joins that is fine. For a reconciliation it is not: phantom stock is precisely the thing you opened this file to find, and a report line that says "14 units of something" helps nobody.

Step 4: Calculate the difference

Another Add Column. Name it 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 5: 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 6: Filter to mismatches

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

This strips out the items where the two sheets agree and leaves only the problems. Put some numbers on it: the system export has 201 SKUs, the count sheet has 198, and 194 SKUs appear on both. The full join therefore returns 205 rows, made up of those 194 plus 7 system-only SKUs and 4 that only turned up in the count. If 175 of the shared 194 agree exactly, this filter leaves 30 rows: 19 quantity mismatches, 7 never counted, 4 phantom. Each one tells you exactly what is wrong: the SKU, the system quantity, the physical quantity, the difference, and the status.

Step 7: Clean up the output

Select Select Columns from the Columns group. Pick the columns that matter for the report: sku_final, product_name, system_qty, physical_qty, difference, status, location. Leave out sku and sku_1, which have served their purpose, and counted_by unless you need it for the audit trail. If the header sku_final looks odd on a report, add a Rename Columns step and call it sku again.

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, sku = sku, right columns left empty)
3. Add Column (sku_final: COALESCE("sku", "sku_1"))
4. Add Column (difference: system_qty minus physical_qty, both COALESCEd to 0)
5. Add Column (status: match / surplus / shortage / not_counted / not_in_system)
6. Filter (status != 'match')
7. Select Columns (clean output for the report)

Setting it up takes a couple of minutes and the join itself runs in milliseconds thanks to DuckDB. When the next physical count lands, load the new file and the same chain runs against it.

Two things carry the whole workflow. The FULL OUTER JOIN, because an INNER JOIN silently hides items that exist in only one file, which is exactly the problem a reconciliation exists to catch. And the COALESCE, because the join hands those one-sided rows to you with their key in whichever of the two SKU columns happens to hold it.

Reconcile your inventory files →