Hello Group Members,
Both Join and Compare can be applied to match fields and data across two files in IDEA.
However there are subtle differences where the two can be applied to derive maximum benefits from each of the functionalities.
Join can be applied to match two files in IDEA where the fields to compare could be Numeric, Character, Date or Time format. As an example I would like to match Withholding Tax Payments (so Payment Date) in File A with Due Dates for Payment in File B to identify delay in payment of Statutory Dues. Here both the fields to match are in Date format. Taking another example I can match Application System Access First Login Time from File A with Attendance In Time in File B to identify unauthorised system access. Here both fields are in a Time format.
Coming over to Compare, Compare can link two files in IDEA primarily to match only Numeric fields. So as an example I can reconcile Sales Order Quantity from my Sales Order File with Dispatch Quantity from my Sales Billing Dump to identify short or excess sales vis a vis the order size. Here both fields are in Numeric format. Another example would be to match Budget Vs Actuals across two files to identify overruns or the Closing Balance for two Trial Balances for Q1 and Q2 to identify material variations in a Ledger Scrutiny.
The other important point we need to consider is that Join does the match at a line item level. So if I have two two line items in a Fixed Assets Register (F A Register) and another Master file with the Depreciation Rate. By executing a Join I can bring the Depreciation Rate into the F A Register at a line item level by matching the Asset Code from F A Register with the Master File. This is to enable Current Depreciation compute in the F A Register.
Compare on the other hand is designed to perform a match at a summary level. So if I am given two General Ledger (GL) files for Q1 and Q2 and I am required to identify material variations + or - 25% between the GL Account Heads, Compare can match the two files at a GL Account level and present Total Closing Balance GL Account wise for Q1 and Q2 with the difference in the output file. So it executes the task at a global summary level more like a Pivot + V Look Up + Field Manipulation all bundled into one dialog box in IDEA.
Regards
Group Admin