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
auditstage SUM
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: