Compare Files content on SUMmarized Amounts
Posted: Fri Jan 18, 2008 6:50 am
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
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