Reconcil
Data alignment and reconciliation
Reconcil is a data matching software designed to compare two distinct lists of data records.
For this comparison to be possible and meaningful, the lists of data records must share a fundamental common point: an "alignable" field. This specific field, called the "reconciliation key" in the software, acts as a bridge between the two lists, representing the same reference entity or reality in both systems.
Using this key, the software can reliably associate each record in one list with its corresponding record or records in the other.
Once this connection is established, it becomes possible to analyze and compare the other information linked to this same key in each list.
In practice, Reconcil allows you to compare these two lists of data records to verify that they correspond correctly or to highlight the differences between them.
Based on this assessment, one might stop there and learn from the differences that have been highlighted.
For example, reconciling the results of two experiments conducted at different times.
In other cases, if no differences between the two lists of data records (according to the reconciliation key) are expected, then Reconcil flags the discrepancies and prompts the user:
- to investigate the causes of these differences
- and correct the data at its source.
This part of the work is called the reconciliation between the two data sources.
For example, reconciling a physical inventory with an accounting system.
For example, the key can be the fixed asset accounting number which is present not only in accounting but also in the physical inventory of fixed assets. Thus, Reconcil makes it possible to align and match fixed assets inventory data and accounting data.
Reconcil takes, as an input, two text files which are lists of records, for example in CSV format. For each record in a list, Reconcil associates zero, one or more records from the second list and vice versa, based on the key common to both lists.
After configuring the two files and various criteria, an alignment is made upon opening of the dataset.
Automatic reconciliation, which can be requested immediately, solves all or part of the reconciliation problem.
The alignment can result in having one record from one list aligned with exactly one record of the second list. But it can also result in having n records from the first list aligned with m records of the second list. Aligned records share the same key value.
For the example, this makes it possible to compare an accounting record representing several assets with one or more physical inventory records.
Alignment can be improved using secondary criteria and manually by permutations or alignment breaks.
Then, one can examine the remaining records to understand why matching records were not found in the second list.
As you resolve cases of misalignment, you can manually set the status of records:
- lone, a record in one list has no correspondent in the other list,
- not examined, a record in a list has not yet been examined,
- ignored, a record in a list has been examined and is deliberately ignored,
- problematic, a record in a list is the subject of a questioning requiring an investigation,
- partly reconciled, a record belongs to a group of records linked by a common key value, but only some records within this group have found their match in the other list.
- strictly reconciled, a record in one list belongs to a group of records which have all been matched with all the records in the other list.
- the alignment view is used to view records from a list with their corresponding records in the other list, but also lone records and records which are not lone but which have no correspondent in the other list,
- the pairs of items view is the set of all records in a list which have a correspondent in the other list,
- the not aligned items views show the records which are not aligned with any records,
- aggregates views present, for example, for each key value, the number of records of a list having this key value, the sums of the corresponding values…
- the synthesis view combines alignment and aggregation: for each key, for example, it reports the number of records in the first list and the number of records in the second list. The user can also aggregate other numeric fields.
For this comparison to be possible and meaningful, the lists of data records must share a fundamental common point: an "alignable" field. This specific field, called the "reconciliation key" in the software, acts as a bridge between the two lists, representing the same reference entity or reality in both systems.
Using this key, the software can reliably associate each record in one list with its corresponding record or records in the other.
Once this connection is established, it becomes possible to analyze and compare the other information linked to this same key in each list.
In practice, Reconcil allows you to compare these two lists of data records to verify that they correspond correctly or to highlight the differences between them.
Based on this assessment, one might stop there and learn from the differences that have been highlighted.
For example, reconciling the results of two experiments conducted at different times.
In other cases, if no differences between the two lists of data records (according to the reconciliation key) are expected, then Reconcil flags the discrepancies and prompts the user:
- to investigate the causes of these differences
- and correct the data at its source.
This part of the work is called the reconciliation between the two data sources.
For example, reconciling a physical inventory with an accounting system.
Explanations
Reconcil is a software which makes it possible to align and then to reconcile two lists of data records sharing a key, i.e. having information in common.
For example, the key can be the fixed asset accounting number which is present not only in accounting but also in the physical inventory of fixed assets. Thus, Reconcil makes it possible to align and match fixed assets inventory data and accounting data.
Reconcil takes, as an input, two text files which are lists of records, for example in CSV format. For each record in a list, Reconcil associates zero, one or more records from the second list and vice versa, based on the key common to both lists.
After configuring the two files and various criteria, an alignment is made upon opening of the dataset.
Automatic reconciliation, which can be requested immediately, solves all or part of the reconciliation problem.
The alignment can result in having one record from one list aligned with exactly one record of the second list. But it can also result in having n records from the first list aligned with m records of the second list. Aligned records share the same key value.
For the example, this makes it possible to compare an accounting record representing several assets with one or more physical inventory records.
Alignment can be improved using secondary criteria and manually by permutations or alignment breaks.
Data review and status assignment
Filters make it possible to hide the matched records or the ones we wish to ignore.
Then, one can examine the remaining records to understand why matching records were not found in the second list.
As you resolve cases of misalignment, you can manually set the status of records:
- lone, a record in one list has no correspondent in the other list,
- not examined, a record in a list has not yet been examined,
- ignored, a record in a list has been examined and is deliberately ignored,
- problematic, a record in a list is the subject of a questioning requiring an investigation,
- partly reconciled, a record belongs to a group of records linked by a common key value, but only some records within this group have found their match in the other list.
- strictly reconciled, a record in one list belongs to a group of records which have all been matched with all the records in the other list.
Data visualization
The data can be viewed in various views:
- the alignment view is used to view records from a list with their corresponding records in the other list, but also lone records and records which are not lone but which have no correspondent in the other list,
- the pairs of items view is the set of all records in a list which have a correspondent in the other list,
- the not aligned items views show the records which are not aligned with any records,
- aggregates views present, for example, for each key value, the number of records of a list having this key value, the sums of the corresponding values…
- the synthesis view combines alignment and aggregation: for each key, for example, it reports the number of records in the first list and the number of records in the second list. The user can also aggregate other numeric fields.
Export of results
The reconciliation results can be exported in XML format, visible in an MS Excel or Libre Office Calc spreadsheet. These results can be provided to operators who, through their knowledge of the source data, can identify anomalies and proceed with the necessary corrections.
Step-by-Step reprocessing and validation
Once the source data has been corrected, a reprocessing operation can be carried out with the new data, allowing a new reconciliation to be requested from Reconcil. The complete reconciliation can thus be performed step by step.