tick and tie / compare multiple tables to find differences - financial reconciliation
I am looking for an example of the simplest way to load several data sources that should all align and present the user with an interface to quickly find discrepancies.
The basic problem is this: financial numbers are recorded in several systems then combined to a single system, which, fortunately, preserves the origin of the data. So the problem can be simplified to comparing just two tables: Source and Destination.
both tables have 3 fields that are used as the reference to each transaction; e.g. company.department.account - so I will likely concatenate that into a single key.
the goal is to be able to display those fields along with the matching/nonmatching like this:
and other approach of creating a link table with the source tables all loaded and linked back using a concatenated key.
This problem is made a little more complex because of many-row relationships: we are joining transactions and summing them up, not just comparing 1 row from table 1 to another row from table 2. In addition to the 3 reference values of company.department.account, we have a time dimension to consider as overall filter.
I am hoping some one here recognizes this kind of problem and can advise best approach to creating this financial reconciliation application.
