Compare Files content on SUMmarized Amounts

[DLLAND]

We have 2 files containing Contract Nbr, Asset Nbr, Invoice Nbr, Invoice Amount

1.
We want to compare the total invoice amount per contract between the 2 files and if the total amount is not equal create a exception table line containing the Contract number and SUMmarized total. How can I get these fields in the exception file.

2.
More of the same
we want to COUNT the number of invoices per contract number
COUNT(DISTINCT(BILLING1.INVOICE_NUMBER)
if not equal, create exception file containing the countract number + the number of invoices.

3.
Please advise and include if possible the sample or instructions on how to create the filter SUM and GROUP BY + create exception table columns

In Auditstage we select
BILLING1 BASE
BILLING2 NOT BASE
BILLING1.CONTRACT_NBR EQUALS BILLING2_CONTRACT_NBR
SUM(BILLING1.INVOICE_AMOUNT) NOT EQUALS SUM(BILLING2.INVOICE AMOUNT)

In the column definition we want to
BILLING1.CONTRACT_NUMBER L1_CONTRACT_NUMBER
SUM(BILLING1.INVOICE_AMOUNT) L1_TOT_INVOICE_AMOUNT
BILLING2.CONTRACT_NUMBER L2_COUNTRACT_NUMBER
SUM(BILLING2.INVOICE_AMOUNT) L2_TOT_INVOICE_AMOUNT

Addition (Workaround)
what I Did was create a Output Summary Table for both tables and compare these 2 exception tables, is there a faster way to do this in one filter.

Regards
Piet Basten

[ray.wurlod]

DataStage is the better tool for this task which, again, is not really a profiling task. Profiling is "discovering what's really out there". Two file comparisons don't really come into it until you're profiling for foreign key candidates.

In DataStage bring the two sources together in a Transformer stage that has a constraint that the two sums are unequal on the output that leads to the exception file.