Set record number in a field

Robert van den…

Forums

I got a bank transaction file in which only the bank statement number is a field. If i want to join this file with the administration i need to have the row/record number in a field so i can create a key field on which to join on. 
Question 1 is how can i create a field with the row number and question 2 is how do i make it that when the field with the bank statement number changes the row number starts with 1. In the perfect world it should looks like this:
Bank statement nr.      Row number
180001                           1
180001                           2
180002                          1
180003                          1
 
Anyone who has any ideas?

Robert van den… Thu, 06/20/2019 - 09:38

no, i found the GetPreviousValue function. But when i use it to use the previous value and add 1 extra for each row when there is a value (with the IF function) i get all error in the fields. I can get my head arround this because in Excel this is no problem. In the attached picture thats the point where it still works. I'am now going to look at that script  of the audit number.

Brian Element Thu, 06/20/2019 - 12:00

Hi Robert,

IDEA isn't Excel so how something works isn't always the same.  What you want to do is usually done through a script.  I recommend you check out the script that I linked to in the previous post and see if that works for you.

Brian

Robert van den… Fri, 06/21/2019 - 05:45

Hi, Brian,
I understand the IDEA isn't Excel, i only want to understand the logic behind a function like the get previous value function in IDEA. It seems that this function only takes the previous value once and it is not a continuous process what seems logic when i think about it.
The script you shared yesterday works for the purpose i want it to. The only thing i struggle with is that i don't want a dialog box and at the moment and i don't quite understand the way this script works because of my basic knowledge is not that great.

Brian Element Fri, 06/21/2019 - 07:22

Hi Robert, I want to appolizie for my previous comment, I was a bit short, I wasn't feeling well, should have waited and the taken out the first part.

The difference is the way Excel does equations versus IDEA using virtual fields.  IDEA really looks at records and usually not at the entire database as an auditing software a record is the transaction which may not be the case in Excel.  So when using the @GetNext or @GetPrevious you can only get items that have already been created, you can't access the previous @GetPrevious as you could within Excel.  So you can't use these functions to create a running total or numbering items like you want, the only way I know to do this if through a script, hopefully that makes sense.

To get rid of the menu in the script find the line Call Menu() and place a quote(') in front of it, that will comment it out and you won't see the menu.  You then need to replace the variables which are needed, namely the filename and the fieldname, so under the Call Menu() add these lines:

sFilename = "This is my file.IMD"

sFieldName = "MY_FIELD"

and that should get rid of the menu and allow you to run the script.

Thanks

Brian

Robert van den… Fri, 06/21/2019 - 10:41

Hi Brain,
No problem about the previous comment. I work in a organisation where the love Excel and are scared of new things. The last 8 years i have been using IDEA for my work (auditing) and the last 10 months i put a lot of effort in data analysis with IDEA. In that proces I run into problems and challenges where my colleagues often think Excel is the better and faster solution for the future, so 50% of my time i'am trying to convince my colleagues that IDEA is better than Excel. So thats the reason i compare IDEA to Excel. I know this is comparing an apple with a pear. But when I start about databases, tables, fields child-parent relations and records my supervisor(s) and senior colleagues often look if they see a ghost. I am very happy with the help you offer, without that I would not have got so far with data analysis with IDEA as where I am now.
a question about the quote('). Is it possible to make multiply lines a comment instead of placing a quote before each line? I found out that in Python lines between a triple quoted is treated as a commend. In my scripts in the sub main i got a lot of call's and sometimes i just want to test a new added call and not the entire script. Is there in IDEA a similair way to easily make multiply lines a comment? 

Brian Element Fri, 06/21/2019 - 12:59

Hi Robert,

Unfortunately that sounds like most organizations, they believe Excel can do it all when it can't and IDEA was created as an auditing tool so it has built in functions for just that.  I have attached some links below that compare Excel with IDEA that might be of interest to you.

Unforunately in IDEAScript you need to place a quote in front of each comment line.  IDEAScript is fairly old now, this is why they are slowly switching over to Python as their programming language.  So when it was originally created that was the standard but things have changed but you still need to place a quote in front of each comment in IDEAScript :-)

Depending if the call needs items from other calls I would paste it at the top of the sub main and then have an exit sub under it so I could just test that item, sometimes I will need to populate variables to make sure the call runs correctly.

Hopefully that helps out a bit.

Brian

https://www.youtube.com/watch?v=PQ_s_GfC_tQ

https://www.youtube.com/watch?v=qZ11t9rSCsE

https://idea.caseware.com/is-excel-a-good-statistical-tool-maybe-not/

https://auditware.co.uk/why-you-shouldnt-use-excel-for-your-data-analytics/

osaajah Mon, 06/24/2019 - 04:23

Hi robert,
You should create a new numeric field and fill "@Recno()" as the parameter. @Recno is a function to return the logical record number in the file. See attached picture.