User guide

Comparing two files

See which rows are new, which have gone, and which changed between two versions of the same export, with both values side by side.

What comparing is for

Joining brings columns together. Comparing answers a different question: given two versions of the same data, what actually changed?

This is the work behind reconciling this month’s export against last month’s, checking a vendor file before and after they corrected it, or confirming that a re-export of the same evidence matches the original.

Running a comparison

Comparing is part of the join tools, so it needs Pro. Open both files so each has its own tab. Click Join in the toolbar, then choose Compare the two files from the list of ways to combine. Pick the key column on each side, which is whatever identifies a row: an invoice number, an account id, a case reference.

The two files do not need the same key column name. You can compare id on one side against ref on the other.

What the counts mean

The panel shows four counts before you apply anything.

• Changed means the key exists in both files and at least one other value differs.
• Only in <this file> means the key is in the file you are looking at and not in the other, so the row has gone.
• Only in <the other file> means the key is new.
• The same means the key is in both and every compared value matches.

The counts are often the whole answer. If you expect three hundred new records and the comparison reports two thousand, something upstream has changed and you have found it before it reached a report.

Which columns are compared

BigXL compares every column the two files share, excluding the key columns. A column that exists in only one file is not a difference, because there is nothing to compare it against.

Reading the result

The preview orders differences first: changed, then added, then removed, then unchanged. On a large file the first screenful should show you the differences rather than a thousand identical rows.

Apply the comparison and it opens as a new tab containing:

• the key column
• a compare status column reading changed, added, removed or unchanged
• for every shared column, both values side by side, as amount and amount (other)

Where the original file already has a column called status, the comparison’s own column is named compare status instead, so no two columns in the result share a name.

The result behaves like any other file. Filter the status column to changed and export that, and you have a change list to send to whoever needs it.

When the key repeats

Everything above assumes a key appears at most once per file. When it does not, the question ‘did this row change?’ has no single answer.

BigXL counts repeated keys and tells you before you trust the result, for example ‘26 keys repeat, so those rows are paired every way they can be.’ Every pairing is shown. BigXL does not choose one candidate match and present it as the answer.

If that is not what you want, the fix is to pick a key that does not repeat, or to remove duplicates first.

When rows have no key

Some rows arrive with the key field empty. BigXL reports them, for example ‘9 rows have no key, so nothing can be matched to them.’

Those rows appear in the result on their own side, unmatched. They are never matched to each other, because two empty keys are not the same key. This is worth knowing because a plain SQL comparison drops such rows entirely and says nothing, which makes the row counts fail to add up with no visible explanation.

What is not touched

Both source tabs are unchanged, including their timelines. Comparing reads the two files as they currently appear on screen, so filters and masking you have applied are respected, and writes its answer into a new tab.