Extraction of specific entries

EbiF

Forums

Hi,

I'm new to IDEA-Script and haven't been able to find a relevant post in the forum. I hope you can help me. I have a table with entries like these:

Date GroupNo EntryNo AccountNo Amount
11/01 4711 1 123 100.00
11/01 4711 2 123 -100.00
11/01 4711 3 234 200.00
11/01 4711 4 0 0.00
11/01 4711 5 234 -200.00
11/02 4812 1 123 -300.00
11/02 4812 2 0 0.00
11/02 4812 3 123 100,00
11/02 4812 4 123 300.00

I want to extract the records where entries within the same date and group, only if the have the same account number and opposite amount and a transaction with an amount of 0.00 between them. In the example above, these would be entries 3 and 5 on November 1st and entries 1 and 4 on November 2nd. How can this be accomplished with IDEA-Script?

Thanks a lot for suggestions,
Eberhard

Brian Element Fri, 11/21/2025 - 14:04

For the reversals do they have transactions in between them such as the 3rd transaction for Nov 2nd of $100.  Also do the 0 transactions not have an account number?

EbiF Wed, 12/03/2025 - 07:43

Hi Brian,
sorry I was on vacation last week and couldn't reply sooner.
Yes, there are transactions between cancellations. Transactions with a value of 0 don't have an account number.

Brian Element Sat, 12/06/2025 - 10:18

Ok, I have a solution for you but I think it might be too simplistic.  It does work for the example entries that you gave me.

On your main file add two virtual fields:

  1. Name: RECNO - Type - Numeric (must be numeric, don't use virtual numeric), Parameter - @Recno()
  2. Name: ABS_AMT - Type: Numeric - Decimals: 2 - Parameter: @abs(AMOUNT)

Select Duplicate Key Detection and make sure you select the "Exclude duplicate items".  The key is DATE - GROUPNO - ACCOUNT_NO - ABS_AMT and the criteria is ABS_AMT <> 0 - this is to isolate any transactions that are one offs.

Join the main file with the file the duplicate key detection file.  The matches is on RECNO and select no secondary matches at the option.  This will get rid of any none duplicate items.

To the file just created add on the following fields:

  1. Name: BEFORE - Type: Numeric - Parameter: @if(ACCOUNTNO = "0"  .AND. @GetNextValue("DATE") = @GetPreviousValue("DATE")  .AND. @GetNextValue("GROUPNO") = @GetPreviousValue("GROUPNO")  .AND. @GetNextValue("AMOUNT") = -1 * @GetPreviousValue("AMOUNT"), @GetPreviousValue("RECNO") , -1)
  2. Name: AFTER - Type: Numeric - Parameter: @if(ACCOUNTNO = "0"  .AND. @GetNextValue("DATE") = @GetPreviousValue("DATE")  .AND. @GetNextValue("GROUPNO") = @GetPreviousValue("GROUPNO")  .AND. @GetNextValue("AMOUNT") = -1 * @GetPreviousValue("AMOUNT"), @GetNextValue("RECNO") , -1)

These two fields isolate the record number for the transaction before and the transaction after.

From the above file perform an extraction, call it after, the criteria is BEFORE <> -1 and only select the AFTER field.  Rename the after field to RECNO

Do the same extraction except instead of including the AFTER field you want to include the BEFORE field.  Rename the field to RECNO.

Append the before and after files together.  Since the fields are the same name once the append is complete you should have the record numbers for the before and after 0 transaction that are reverse.

Take the original file and join it with the appended file, the match is on RECNO and the type is matches only.  In the scenario you gave me it gives me the results that you are looking for.

Thanks