Extracting Reversed payments

jsowoya

Forums

I have a table with close to 300,000 rows, and full of reversed payments and then the some payments are corrected, as show on the file on my dropbox https://www.dropbox.com/s/wqzkwiismn8y7k7/IDEA%20HELP.xlsx  .   How would I go about this? Does it require writing a IDEA script or just @function ? Please help with detailed steps. 
In summary, It's  about extracting reversed payments on a bank statement into a sheet , and remain with only paid or corrected entries on bank statement.

Brian Element Sat, 02/08/2014 - 21:11

Hello jsowoya and welcome to the site.

Thanks for attaching a copy of your data as that helps out.  What I would do is create a virtual field that calculates the absolute amount using @abs(AMOUNT_DR_CR), I would then perform a duplicate key detection using the CHEQUE_NO and the ABS_AMOUNT to pull out the list of reversed cheque payments.  I would then do the same thing using the duplicate key detection but this time selecting Output records without duplicates to get the list of transactions made.  The only problem might me if you have the following scenario:

Cheque No Amount_DR_CR
31200 92,000
31200 -92,000
31200 -92,000
31200 -9,200

As the List of reversed cheque payments would contain the 3 items.  If you do have this scenario let me know as I have an idea how you can handle it.

I have attached the imd files with the data you included so you can see how I obtained the result.

jsowoya Sun, 02/09/2014 - 03:46

Dear Brian,
My data is very complicated as it contains transactions of the senario you have just outlined , as follows:

Cheque No
 Amount _DR/_(CR) 

31200
92,000.00

31200
-92,000.00

31200
-92,000.00

31200
-£9,200.00

I would appreciate your help on the above scenario, because once the reserved transactions are pulled (i.e the first two trnx) ,  the remaining two transactions ( -92,000.00 and -9,200.00)  I will consider them on either  " list of transactions made" or "suspected list of inaccurate data captured and requiring further probe and verify which of the two is correct". 
Many thanks, Brian.
 

Brian Element Sun, 02/09/2014 - 07:22

Hello jsowoya,

Just a couple of question so I can better understand your data.  So would you consider anything that is out of balance as being an error? In which case you could use the summary functions and summarize on amount or could you have transactions that are in balance and could be in error?

Also another thought is if you do a summary using my ABS_AMOUNT field, so you would summarize on Cheque_no and ABS_AMOUNT field, you would then extract any no_of_recs that is not divisable by 2 using NO_OF_RECS % 2 <> 0 which would extract all items that only have 1, 3, 5, etc items.  Using this would flag the above scenario as it would flag transaction 31200 as having 3 entries for 92,000,000 and one for 9,200,000.

If you have more data with the different possible scenarios I will try and give you a hand.

Thanks