Identifying Transactions that Back out Other Transactions
Forums
Hi there,
I am relatively new to using IDEA for analysis, and I've been trying to figure out this testing, but I'm not sure if I'm just missing an obvious solution, or maybe there's another way to do this testing.
I am analyzing travel expenses and I want to identify and extract entries where every other field is the same (i.e. report key, expense type, travel start date, travel end date, transaction date, etc.) EXCEPT for the expense amount fields.
Basically, I'd like to identify and extract records where the two following scenarios occur:
1. Entries where the expense amount column has a positive and negative amount of the same value (i.e. 500 and -$500) and
2. Entries where the expense amount column totals $0. And by this I mean that I noticed that some transactions might have more than 1 entry to back out an existing expense. (i.e. 1000 and -800 and -200).
If #2 isn't possible, that's ok, but at least #1 would help us reduce the number of transactions that we might end up investigating only to find that they were backed out.
Once I identify these back out transactions, I can then filter them out of the original dataset so we don't pick up those transactions for testing.
I hope that's clear, but any help or advice would be appreciated.
Thanks!
Hi Kelly,
Hi Kelly,
To your main file I would add a Recno virtual field to it so that you can extract your items from the main database. You can create a virtual field using the parameter @Recno() and that will add the record number to your file.
For the first one I would create a virtual field that contains the amount field as an absolute. The parameter would be @abs(AMOUNT_FIELD). I would then do a duplicate key extraction using your criteria, such as report key, expesne type, travel start and end date, etc along with the absolute amount field. This will give you all the transactions that have the same amount. Also to validate I would then do a summary based on the same criteria and total and the original amount field. If the amount total is 0 then your entried should be offsetting (you can also check to make sure there are only two for each duplicate).
For item 2 I would do the duplicate key but not use the amount field, this will give you all the expenses within the same trip. I would then do a summary like in the first part and any items that are 0 would be items that were reversed.
You can then remove the items you have found from the main population. Hopefully that makes sense.
Thanks
Brian