ArrayCount - wish list

I just keep finding times when I want to use ArrayCount, a wish list item I posted years ago. I’ve always found ways to accomplish it, but it would be so useful to use such a Function to find out how many times various elements occur within an array.

It should be rather easy to create a functiion using something along these lines:


theArray would be the array name while theText would be the element text to search for and theSep is the separator used in the array. Using the extract( function assures that if there is no such element in the array it returns a 0.

Since array separators aren’t limited to a single character anymore, you could also use


Dave Thompson wrote an ArrayElementCount custom statement some years back - it’s currently in my collection of custom statements and functions to load when Jim gives us the knowhow with the next version. The code is posted below

I’ll look into making an Extract( function - unless you want to do it Gary?

  The ARRAYELEMENTCOUNT statement takes an array with duplicated elements and returns a 
  deduplicated array, where each element consists of an element from the
  original array and a frequency count. The frequency count is the number 
  of times the element occurs in the original array. The original
  element and the frequency count are separated by a joiner of one or 
  more characters.
  <PARAMETER NAME=InputArray TYPE=TEXT> The original array, with all 
  the elements </PARAMETER>
  <PARAMETER NAME=OutputArray TYPE=TEXT>  A field or variable to receive 
  the deduplicated array with frequency count </PARAMETER>
  <PARAMETER NAME=Sep TYPE=TEXT> The array separator </PARAMETER>
  <PARAMETER NAME=Join TYPE=TEXT> The joiner between each element and 
  its respective frequency count </PARAMETER>
The statement<p>
arrayelementcount "a,b,c,a,d,b",AVariable,","," - "
will set AVariable to: <p>
"a - 2,b - 2,c - 1,d - 1"



// Submitted by David Thompson

Local x,InputArray,OutputArray,Sep,Join
if error rtnerror info("error") endif
if error rtnerror info("error") endif
if error rtnerror info("error") endif

ArraySort InputArray, InputArray,Sep
ArrayDeDuplicate InputArray, OutputArray,Sep
ArrayFilter OutputArray,x,Sep,arraysearch(InputArray,import(),1,Sep)
ArrayFilter OutputArray, OutputArray,Sep,import()+Join
        +str(val(array(x,seq()+1,Sep))-val(array(x,seq(), Sep)))
SetParameter 2, OutputArray
if error rtnerror info("error") endif

The extract( function already exists. extract("a,b,c",",",2) returns b, and extract("a,b,c",",",-1) returns 3.

Whoops! I misread Gary’s post.

I realized this morning, that I was expecting some of those separators to do double duty. It should be


Thanks for the responses; I’ve been on the road for a few days and had no earlier chance to respond myself.

David’s Custom Statement is exactly what I needed. I thought it existed but couldn’t remember where I had used it before and searches for “arraycount” obviously didn’t turn it up.

Among the ways it’s handy is, for instance, to create an array of cities in a database, then see how many times each city occurs. Currently I’m using it to build a chart to show the frequency of the “Sources” for clients in a database in order to evaluate marketing results.

See - Dave solves our problems while he’s sound asleep :slight_smile:

If you had a field named City, and another field that was always not empty (Status in my example), you could generate a list of the number of appearances of each city with this:

field City
Field Status
outlinelevel 1
displaydata arrayselectedbuild(cr(), “”, {?(info(“summary”)=1,Status+tab()+City,"")})

Actually there are many easy ways to do it if I want to crunch data. The objective is to sit on a single record and be able to instantly display a chart based on the number of whatevers. Arrays are pretty much the onl way to do that.

I’m beating this horse again…because I’m back to the problem.

This time I have several hundred records of people with their ages. Some people are in the list repeatedly but it’s easy enough to build an array with one entry for each person. So I end up with a list of ages. Some ages might be there several times, some might not be there at all. I need to get a list showing me how many times each listed age exists within the overall array.

It’s easy to pick a single age then find the number of times it exists - and the processes suggested above will do that with a value assigned to theText. But if I want to determine how many times every age within the array occurs, it’s not easily accomplished with anything that is in Panorama as installed on other users’ computers.

So an array of a,g,t,h,i,v,e,g,g,e,a,j could be processed to produce a result such as a=2,g=3,t=1,h=1,i=1,b=1,e=2,j=1

It would equate to a Group and Count.

What you want can easily be done in the data sheet: Put your array in a variable, open it as a (temporary) database, do your sorting and grouping and counting — done.

Agreed. There are a number of ways to get it done including looping through the array using David’s arraysize(theSep+replace(theArray,theSep,theSep+theSep)+theSep,theSep+theText+theSep)-1.

I still wish for a Statement or Function that would do it. And considering the number of times I encounter the need, I would expect it to be useful for others.

This could work in a statement:

let myArray="a,g,t,h,i,v,e,g,g,g,g,g,g,g,e,h,v,a,j"
let tempArray=""
looparray arraydeduplicate(myArray,","),",",element,index
message arraystrip(tempArray,",")

☞ a=2,e=2,g=8,h=2,i=1,j=1,t=1,v=2

Using a function I could only get something to work, but it could not handle multiple consecutive elements with the same value. This will work as long as there are no more than 3 consecutive identical elements.

let myArray="a,g,t,h,i,v,e,g,g,e,a,j"
message arrayfilter(arraydeduplicate(myArray,","),",",

☞ a=2,e=2,g=3,h=1,i=1,j=1,t=1,v=1

The problem here is that arraydeletevalue will only delete the first of two identical consecutive values. I used another nested arraydeletevalue function so at least a group of three could be allowed. I guess you could nest a bunch of these functions but that seems very heavy handed.

It sounds like you are starting from information a database. If that is the case, I think the summarytable( function will do what you want directly, without needing to make an intermediate array of ages.

The summarytable( function is relatively new, so you may not have explored all of the possibilities. Another very powerful function that is new to Panorama X is aggregate(.

I don’t think aggregate( is helpful for this particular problem, but it’s a good new feature to master.

Thanks for that info. I will definitely check it out.