auditstage SUM

Formally known as "Mercator Inside Integrator 6.7", DataStage TX enables high-volume, complex transactions without the need for additional coding.

Moderators: chulett, rschirm

Post Reply
DLLAND
Premium Member
Premium Member
Posts: 9
Joined: Tue Jan 08, 2008 5:17 am

auditstage SUM

Post by DLLAND »

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

Post by ray.wurlod »

Moderator: please move to Profiling forum

AuditStage is a profiling tool. TX is the WebSphere Transformation Extender.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply