IDEA Tech Tip: Dealing with Key Value Extraction Limitation
Q. I have a very large file that has over 2,000 cost centres and I need a separate file for each cost centre. What is the easiest way to do this?
A. Generally, I would say that you should use the Key Value Extraction as it allows you to create a separate file for each unique key, but the Key Value Extraction is limited to 500 keys at a time, so we will have to work around this.
The first thing you need to do is create a summary by cost centre. The Key Value Extraction only works for 500 unique keys at a time, so you want to break your file into files that contain 500 or less key values in each. Once the summary is completed note the following values in your summary, the first value, value 500, 501, 1000, 1001, 1500, 1501 and so on. I just took one of my files and did this by document number.
Next step is to do a direct extract to create your multiple files that you can then run the key value extraction on.
In my example I used the following formula to obtain the transactions for all documents between 58931 to 75494 (record 1 to record 500 of the summary), I am using the @val as my field is a character field holding numeric data.
My second extraction would be documents between 75495 and 76015 (record 501 to 1000):
I do this until you have a file that contains all your cost centres. In your scenario, you should have four or more files. Four files with 500 branches in each and a final file with the number of cost centres up to 2, 500.
You would then perform a Key Value Extraction on each of the five files that would create a new file for each of the cost centres.
Once this is complete you now have a separate file for each of your branches. This takes a bit of time but much quicker than trying to extract each one individually using the direct extraction.
Want to learn more about Key Value Extraction? Check out our tutorial video for more information.
About Alain Soublière:
Alain Soublière has many years of experience working with computer audit software. He worked in a senior management role as the IDEA Product Manager for many years before becoming Director of Product Strategy for CaseWare Analytics and more recently the Chief Product Strategist.
Connect: Alain Soublière