Hi,
I would like to remove journal entries which net to zero from my database, such as accruals and reversals, from a GL using Duplicate Key - Exclusion.
First of all I have created column with absolute values (ABS) and second column for debit and credit entries (DEBIT_OR_CREDIT).
Then using Duplicate Key Exclusion -
Fields to match: ABS
Field that must be different: DEBIT_OR_CREDIT
The resulting file represents the items that should be excluded from the population.
The problem is when there are for example entries: 1000, 1000, -1000, -1000 IDEA excludes only 1000 and -1000 and goes to another number. Therefore for excluding all negative values I need to manually repeat Duplicate Key Exclusion more times (Remove excluded entries from original population then run again process on new database and so on).
Thanks for reply,
Marek
Kudrnis
Offline
Last seen: 4 years 9 months ago
Joined: 01/04/2017 - 08:50
Hi Marek and welcome to the site.
One of the limitations of the Duplicate Key Exclusion is that it only picks up the first instance of a record that meets the parameters. I have a script that gets around this that you might want to try out and see if it helps you out.
http://ideascripting.com/Same-Same-Different-Test
Thanks
Brian
Thanks for fast reply, but there is another limitation. It works fine but I need to grab only couples with same absolute value but on debit and credit side. Using simple Duplicate Key Exclusion it works perfectly but I need to repeat it more times to get desired outcome.
For example - find first 1000 EUR entry than -1000 EUR entry repeat another 1000 EUR then opposite. At the end I need that excluded entires will have control total 0.
Any chance you can great a small example with some test data to look at. Also wouldn't you need to match on some type of coding instead of just on values?
Of course - pls see attached small example from my database. My goal is to remove creation a reversal of accruals during year. Therefore Im just focusing on same amounts with opposite sign - therefore only relevant for me is absolute value and debit/credit side.
Regarding to attached example - my goal is to extract highlighted cells (which sum to zero). Using simple Duplicate Key Exclusion - I would get only each combination of numbers once therefore I must repeat whole process again. Using your script I would get all duplicates. Maybe Im doing something wrong.
Hi Marek,
This is not an easy exercise as usually you would have some other field that would indicate that the items go together and not just the amounts. Here is what I came up with, it is not perfect but I was just trying to get the smallest population.
First thing I did was add a record number field to the database so I can reference back to the items.
I then did a duplicate key analysis using the ABS field as the key.
So this gave me all the items that are potential duplicate. I then did a summary by ABS with the Amount field.
So from this we can see we can remove all transactions that have an ABS of 90,257.85.
I then used my script that I posted above to give me the transactions that are not in balance and have both a credit and a debit. So the same field was ABS and the different field was DEBIT_OR_CREDIT. This is the result.
Now this is where I think it takes manual intervention. One way I see of doing this is adding a boolean field and indicating which records to keep and then do a join to the original file to remove the records that net to 0.
Hope this helps a bit.
Brian
One last question for boolean field - is it possible to select and tick multiple cells?
No, I don't think there is a way.
Thanks a lot. This could be the best approach. Unfortunately it is not possible with manual intervention. Im working with huge databases so automated way would be ideal :). But thanks again!