DSXchange

we loaded the files in MSSQL using DataStage.
In AuditStage we want to compare the content of the fields.

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 + column

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


Regards
Piet Basten

Moderator: please move to Profiling forum

AuditStage is a profiling tool. TX is the WebSphere Transformation Extender.