Add Counting Column

Brian Element
Background

This script will add a new column that will count the number of records based on a field that is selected by the user.

Documentation

This script will allow you to select a file and a field in which you want to count the items.  It will add a row and place the number of that particular entry.

If we use the ACCESS-Database.IMD that is used in the training we can see from the above that for the COUNTRY field we have Albania, Andorra and Angola.  We run the script and will get this dialog in which we select the file and the country field.

Once we run the script a new field called COUNT will be added and you will have the following:

So from the above example we see that we have 146 records for Albania, 2 for Andorra and at least 6 for Angola (actually 16).  This script will work on character, numeric, date and time fields.

Brian

myrahz Tue, 08/01/2017 - 06:28

Thanks for this, this is exactly what I was looking for, I knew I couldn't be the one wanting to do this so I decided to check this forum, wasn't disappointed :p
Thanks once again!

myrahz Tue, 08/01/2017 - 09:35

It is really helpful when I want to create a pivot table showing multiple registers for the same unique key, with this count I can display them as columns, for instance 2 people with same unique ID, I can list 1 register per unique key and list whatever as column.In regard to this, is there any way to create a Pivot Table with anything else beside a numeric as values?
Something like this
http://imgur.com/a/iQRrD

Brian Element Tue, 08/01/2017 - 10:29

You can drop non-numeric fields into the data but it only gives the count so I don't think that is what you are looking for.  So it is really limited to numeric fields at this time.

ubuntuai Fri, 08/04/2017 - 02:32

What version of IDEA was this written in? Will it work in version 10.x? Sorry for asking before trying. I have a script for calculating running balances that gives me errors when I try using it in version 10.x. I have not been able to modify it to work. This is very similar so I can adapt it if it works in 10.

ubuntuai Sat, 08/05/2017 - 03:18

Thanks Brian. Permit me to ask for more: In your free time (if any) could you add additional comments to explain what the various functions are doing?

Brian Element Mon, 08/07/2017 - 07:38

Hi ubuntuai,

I have added some comments, if there are any areas of the script you are not sure about please let me know and I will explain further.

Thanks

Brian

Alister Tue, 04/16/2019 - 07:17

Thanks a lot Brian.
It would have been better if this could w0rk on numeric fields a well (I believe it only works on Character fields)
Also would be nice if you had a script that would show only the final count. (for eg corresponding to all Albania cells, the number 146 should apply instead of cummulative count). For now im using a work around, summarizing and then joining database. But a scrip would make it easier.
Once again, you are the Best ;)

rahulsaxena3 Fri, 08/16/2019 - 06:46

Hi Brain,
Could you please tell me how will i be able to genreate a simillar column of count but usinf two fields instead of just slecting one

Torsten Reifegerste Tue, 08/10/2021 - 09:48

Hello Brian,
is there a way to get this script running without the Dialog Menue?
I tried to separate this script in his two parts (Dialog and Main Function) and replace the fields created throught Dialog Menu but I failed.
 
thx for help,
Torsten

Brian Element Sat, 08/14/2021 - 08:41

Sure, just comment out the get menu function and make sure the filename and field variables are populated before running the rest of the code as that is what the dialog is returning.

Fernando Na Mon, 08/08/2022 - 09:37

Im trying to create a new column that contains a value from another one. I want this value to be repeated by "Codigo". Everytime "Codigo" changes, The "FILE ID" column must have the new FILE ID. This value can be repeated, and "Codigo" can be repeated too.