Results & differences file

Convergence

Convergence is the share of keys that matched completely, out of all keys found in either file:

convergence = matched / (matched + mismatched + only_in_a + only_in_b)

It is a quick health check of the period. If convergence is below 70%, Reconcio adds a recommendation: most often the key columns don't identify the same records in both files, or a condition is set on the wrong pair of columns.

Row counters

The counters cover every key of both files:

Totals of amounts

For numeric compared columns, Reconcio shows the totals in each file and their difference, separately for integer and decimal columns. Numbers are 64-bit, so the totals are exact to the cent.

Key statistics

The quality of a reconciliation depends on the key. These statistics show duplicates, empty values and how well the key identifies records.

Column statistics

Recommendations

Each recommendation has a type and details with the data it refers to.

Differences file

The differences file is a CSV with one row per key. It contains the values from both files side by side and the result of every check, so you can see exactly what differs and send the file to whoever has to investigate.

The columns come in this order:

1. System fields: all_rule_checks (1 — the key matched completely, 0 — it didn't), absent_in_file_a andabsent_in_file_b (1 — the key is missing from that file),rows_in_file_a and rows_in_file_b (how many rows each file had for the key).
2. Key columns, without a prefix, named as in file A.
3. Values for every compared column:file_a_<Column> and file_b_<Column>. A value is empty if the key is missing from that file.
4. Checks for every compared column, named after the condition: eq_<Column>, gt_,gte_, lt_, lte_,ignore_. 1 — the condition passed, 0 — it failed or the key is missing from one of the files.

Example

Key InvoiceId; EQ on Amount andCurrency; GTE on Quantity. The columns of the file:

# System fields (always first)
all_rule_checks    1 = the key matched completely
absent_in_file_a   1 = the key is only in file B
absent_in_file_b   1 = the key is only in file A
rows_in_file_a     rows with this key in file A
rows_in_file_b     rows with this key in file B
# Key column (no prefix)
InvoiceId
# Values from both files, for every compared column
file_a_Amount      file_b_Amount
file_a_Currency    file_b_Currency
file_a_Quantity    file_b_Quantity
# Check results, for every compared column
eq_Amount  eq_Currency  gte_Quantity

Some rows:

all_rule_checks,absent_in_file_a,absent_in_file_b,rows_in_file_a,rows_in_file_b,InvoiceId,file_a_Amount,file_b_Amount,file_a_Currency,file_b_Currency,file_a_Quantity,file_b_Quantity,eq_Amount,eq_Currency,gte_Quantity
1,0,0,1,1,INV-015,200.00,200.00,EUR,EUR,4,3,1,1,1
0,0,0,1,1,INV-003,1000.00,1000.01,EUR,EUR,5,5,0,1,1
0,0,0,1,1,INV-007,500.00,500.00,EUR,GBP,3,3,1,0,1
0,0,0,2,1,INV-042,50.00,100.00,EUR,EUR,1,1,0,1,1
0,1,0,0,1,INV-089,,750.00,,EUR,,2,0,0,0
0,0,1,1,0,INV-210,450.00,,EUR,,1,,0,0,0

• INV-015 — matched completely (4 ≥ 3 passes GTE).
• INV-003 — the amount differs by one cent.
• INV-007 — the currency differs.
• INV-042 — two rows in file A for one row in file B: never matched, whatever the values.
• INV-089 — only in file B.
• INV-210 — only in file A.

Useful filters in a spreadsheet or SQL:

-- only mismatched keys (present in both files)
all_rule_checks = 0 AND absent_in_file_a = 0 AND absent_in_file_b = 0
-- keys only in one file
absent_in_file_a = 1 OR absent_in_file_b = 1
-- keys that appear more than once
rows_in_file_a > 1 OR rows_in_file_b > 1

Statuses