Extraction of specific entries
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
Ok, I have a solution for…
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:
- Name: RECNO - Type - Numeric (must be numeric, don't use virtual numeric), Parameter - @Recno()
- 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:
- 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)
- 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
Hello Brian and a happy new…
Hello Brian and a happy new year!
Thanks for your solution that works perfectly with the example. Unfortunately, my example was too simple. It can happen that several zeros and other amounts in between. My .imd looks like this:
Date GroupNo EntryNo AccountNo Amount
11/01 4711 1 123 +100
11/01 4711 2 123 -100
11/01 4711 3 234 +200
11/01 4711 4 0 0
11/01 4711 5 234 -200
11/01 4811 1 0 0
11/01 4811 7 0 0
11/01 4811 9 0 0
11/02 4812 1 123 -300
11/02 4812 2 123 +300
11/02 4812 3 345 -500
11/02 4812 4 456 +300
11/02 4812 4 678 +200
11/02 4812 8 0 0
11/02 4812 10 0 0
11/02 4812 4 456 -300
11/02 4812 12 345 +500
11/02 4812 15 678 -200
My goal is to identify and extract the bold entries. I guess this must be done with do/loop?
Thanks for help!
Eberhard
Correction, some entryNo…
Correction, some entryNo were wrong:
Date GroupNo EntryNo AccountNo Amount
11/01 4711 1 123 +100
11/01 4711 2 123 -100
11/01 4711 3 234 +200
11/01 4711 4 0 0
11/01 4711 5 234 -200
11/01 4811 1 0 0
11/01 4811 7 0 0
11/01 4811 9 0 0
11/02 4812 1 123 -300
11/02 4812 2 123 +300
11/02 4812 3 345 -500
11/02 4812 4 456 +300
11/02 4812 5 678 +200
11/02 4812 8 0 0
11/02 4812 10 0 0
11/02 4812 11 456 -300
11/02 4812 12 345 +500
11/02 4812 15 678 -200
I have updated my solution…
I have updated my solution based on your new data, the beginning starts the same.
On your main file add two virtual fields:
- Name: RECNO - Type - Numeric (must be numeric, don't use virtual numeric), Parameter - @Recno()
- 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.
3. You then want to create two files, one that contains the transactions with ACCOUNTNO = 0 and the other file is the transactions that contains transactions with an ACCOUNTNO <> 0. I called them Zero items and Non zero items respectively.
4. Join the Zero items (secondary database) file with the Non zero items (primary database), the match is based on DATE and GROUPNO and you want All records in primary file.
5. Next step is to physically sort the database so the matching transactions are next to each other. Use the sort task in the Data tab and the sort is based on DATE, GROUPNO, ACCOUNTNO and ABS_AMT.
6. Add a new field that will be the flag to indicate is the transactions should be removed. I created a virtual numeric field, called it EXTRACT and used the following equation:
@if(date = @GetNextValue("DATE") .AND. GROUPNO = @GetNextValue("GROUPNO") .AND. ACCOUNTNO = @GetNextValue("ACCOUNTNO") .AND. ABS_AMT = @GetNextValue("ABS_AMT") .AND. RECNO < RECNO1, 1, @if(date = @GetPreviousValue("DATE") .AND. GROUPNO = @GetPreviousValue("GROUPNO") .AND. ACCOUNTNO = @GetPreviousValue("ACCOUNTNO") .AND. ABS_AMT = @GetPreviousValue("ABS_AMT") .AND. RECNO > RECNO1, 1, 0))
7. I then summarized the sorted file, the summary was based on DATE, GROUPNO, ACCOUNTNO and ABS_AMT and the numeric field to total is EXTRACT.
8. From the summary file I extract all items in which the EXTRACT_SUM is equal to 2. This would indicate that the transactions is part of a match with a 0 item between them.
9. The final step is to match the extraction back to the original file. The original file is the primary and the extraction file is the secondary. The match is based on DATE, GROUPNO, ACCOUNTNO and ABS_AMT. Match type is matches only. This should give you the list of reversed transactions.
Let me know if you have any problems following the instructions.
For the reversals do they…
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?