tick and tie / compare multiple tables to find differences - financial reconciliation

daveatkins — Tue, 14 Jun 2022 00:50:32 GMT

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:

company.department.account, sourceTotal, DestinationTotal

two approaches I have found so far in googling qlik: a) one big vertical table where the source and destination are concatenated and then self-joined https://community.qlik.com/t5/QlikView-App-Dev/How-to-compare-2-tables-and-put-non-matching-results/td-p/248490

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.

topic tick and tie / compare multiple tables to find differences - financial reconciliation in App Development

tick and tie / compare multiple tables to find differences - financial reconciliation