Copy column with data

MikeF

Forums

Hi all,
I just started using 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 replacement for Excel).
So one key feature of Excel that I use a lot is to copy colums inside a worksheet (to keep the original column) and then use the find & replace to manipulate the data in the cells. For example a column where 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?
- How do I delete a certain word in every cell of a specific column?

I am using IDEA 10.

Mike

Brian Element Sat, 11/26/2016 - 15:21

Hi Mike and welcome to the site and IDEA.

You can do this easily in IDEA and I will walk you through the steps.  I am using the Smple-Employees file for this demo in the Samples project folder.  In the address field you have the following information:

Suppose I want to remove Street from the ADDRESS field.  You first have to go to the field manipulation dialog to append a field.  The easiest way to access it is to double click in the database and the Field Manipulation dialog pops up.  Select Append to add a field.  In this example I will call it ADDRESS_NEW and select Virtual Character with a length of 31.

You have a choice of field types.  Virtual Character is some what like an Excel field in that you can go back and change an equation you create.  If you select a character field then once you create the field it can not be changed.  So which one you use depends on your goals.  If you want to possibly change it in the future then select the virtual character, if you have no plans on changing it than you can use the character field.  Now we must create the equation for the contents of the field.  You click in the Parameter box to open the Equation Editor.  For this equation I will use two IDEA functions, the @Replace and the @Upper.  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.  The other one is the @Replace in which I will tell IDEA to replace STREET with nothing.  So here is what the equation would look like in the equation editor:

Once it is entered hit the Green Check mark and then the OK button on the Field Manipulation and your result will be this:

Hopefully this helps you out.

Brian

Sunder Gee Sat, 11/26/2016 - 21:24

Brian's method will work in every case as described to remove certain words.

A simpler way to met the exact example given by Mike where the text/characters are removed but yet retaining the numeric/digits would be to use the @JustNumbers function.

In Brian's example, you would Append a numeric field and use the formula @Justnumbers(ADDRESS). Just a word of caution though. If the address does not contain a numeric, the formula will return the numeric zero.

There are many ways in IDEA to obtain the desired results.

MikeF Sun, 11/27/2016 - 07:36

Thanks for the replies Brian & Sunder, Both worked out well. 
Just to add some context: I needed this to parse the data as I am setting up an analysis for the accounts receivable after balance date. So the next step for me will be to analyse the total ledger (or a drilled down version for the bank accounts) and then run the invoice numbers and/or invoice amounts as criteria for an "advanced filter (like in Excel). So if you have more tips to get me going, please. If I have the complete analysis going, I will give a presentation in the company I work for on data analysis in IDEA and a comparison with Excel. 

Brian Element Mon, 11/28/2016 - 07:20

Hi Mike,

It sounds interesting.  I am not 100% sure I understand what you would like to do.  Could you give me an example of what you want to do so I can better understand it and hopefully give you some tips.

Thanks

Brian

MikeF Mon, 11/28/2016 - 17:52

Hi Brian, 
At year end there are accounts receivable. In some cases, mostly on clients where we conduct financial audits, the list of outstanding receivables is extensive. Let's say the layout is (simplified); 
name "invoice"+invoicenumber description invoice-amount 
Then I would like to conduct a search with criteria invoicenumber and invoice amount. If they both appear in one row the next year and they originate from the bank ledger, it appears the invoices are paid. I could then provide a trainee with the outcomes of the analysis, to search the bank statement and see if this is really the case. Mostly I use auditfiles as input for analysis, so all the journal entries are one big list. 
 

Sunder Gee Tue, 12/13/2016 - 20:42

I am going to make a few assumptions and then a suggestion.

1. You are providing assurances that Revenue is not overstated via errors in Accounts Receivable.

2. You have an outstanding AR file at year end with invoice number field (Year 1).

3. You can obtain the detail bank account file at some point in Year 2.

4. The detail bank account file in #3 has a reference invoice number.

5. You have tested deposits (or reconciled)  in the detail bank account file (#3) to actual bank statements (either in hard copy or online) and confident that records in the data file gets into the bank statements.

 

Use the Join Databases feature in IDEA with #2 and #3 as the files and select the Matches Only option. Match on fields Invoice Number and Amount.

You end up with a new file of all AR from Year 1 that was received from customers in Year 2 as of the date of file #3. Only unpaid or partially paid invoice numbers (or paid but via several partial payments) will not be in the new file. As such you can acertain the risk of potential errors in #2 and possibly risk of bad debts.

 

With different clients and different data file fields you will have to use different methods to achieve your audit objectives but there is usually a way in IDEA to make it easy for you.

 

 

 

Steven Luciani Thu, 12/01/2016 - 09:18

If you have a date field in your AR file, preferably the date that the recievable was setup, you could simply right click on any record in the date field, and choose Display All Records Containing from the picklist. From the popup window change the mathematical sign to < and choose the date whereby any AR older than that date is material to your audit. This will display all the AR transactions for whcih you are interested in gathering additonal information. You can then extract this file as a separate IDEA file or if your subordinate does not have IDEA, export the subset file to Excel and provide it to that person.

Note also that there is an Aging feature in IDEA under the analysis tab that will produce a great AR aging report.

Good luck with your analysis

Steve