IDEA Tech Tip: Copying and Manipulating Data in a Column
Q: I just started using CaseWare IDEA for data analysis. I used to do everything in Excel, but I see IDEA as a very useful addition in my analysis (and maybe at a certain point even a replacement for Excel). One key feature of Excel that I use a lot is to copy columns inside a worksheet (to keep the original column) and then use 'Find & Replace' to manipulate the data in the cells.
Take the example where in a column you have in each cell the word "invoice" (text) + invoicenumber (digits). In Excel, I would replace the word invoice with "" to remove the word invoice and keep the invoicenumber. How do I make an exact copy of a specific column in IDEA 10 and how do I delete a certain word in every cell of a specific column?
A: You can do this easily with IDEA. I am using the Sample-Employees file in the Samples project folder for this demo. In the address field you have the following information:
Suppose you want to remove Street from the ADDRESS field. First, go to the field manipulation dialog to append a field. The easiest way to access it is to double-click on the database and the Field Manipulation dialog pops up. Select Append to add a field and call it ADDRESS_NEW and select Virtual Character with a length of 31.
You have a choice of field types. Virtual Character is like an Excel field in that you can go back and change an equation you create. If you select a character field, it cannot be changed once you create the field. So your selection depends on your goals. If you think you might change it in the future, then select the virtual character. Else, you can use the character field.
Now we must create the equation for the contents of the field. Click in the Parameter box to open the Equation Editor. For this equation I will use two the @Replace and the @Upper IDEA functions. I use the @Upper as I don't know if I will find Street or street or sTreet-using the @Upper changes it all to STREET. @Replace tells IDEA to replace STREET with nothing. Here is what the equation would look like in the equation editor:
Once you enter the equation, hit the green check mark and then the OK button on the Field Manipulation. The results will look like this:
– Brian Element, CPA, CIDA, CISE, CFE – IDEA Certified Instructor and Financial Advisor at Public Works and Government Services Canada
A: Another way to address Mike’s scenario where text/characters need to be removed is to use the @JustNumbers function. In Brian's example, you would Append a numeric field and use the formula @Justnumbers(ADDRESS). A word of caution: if the address does not contain a numeric component, the formula will return the numeric zero.
– Sunder Gee, CPA, CMA, CIDA – IDEA Certified Instructor and Senior Director, Data Services at RTA Corporation
For more IDEA tech tips or to ask your own IDEA questions, visit IDEAScripting.com.
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