Grouping consecutive values within an array

Panorama X
1

This is reminiscent of Martin Gardner’s puzzles in Scientific American all those years ago.

I have a comma-delimited array of cattle ear tags, each of which may or may not have a leading alpha and always has a trailing numeric of undefined length, for example:

CH451,F8308,F8452,F8453,F8457,F8458,F8459,F8460,287

A typical array has many more elements than this so I want to simplify the list by grouping consecutive values (defined as consecutive numerics, all with the same leading alpha characters). When grouped, the above list would look like this:

CH451,F8308,F8452-8453,F8457-8460,287

I have struggled with implementing this grouping via array manipulation and resorted to using a separate database with a tag in each record. That’s a very messy way to do it and I’d really like somebody to come up with an elegant array-based solution.

2

Well, here’s my take on the problem:

local earTags,tempArray
tempArray=""
earTags="CH451,F8308,F8452,F8453,F8457,F8458,F8459,F8460,287"
looparray earTags,",",element,item
    If element beginswith striptoalpha(arraylast(tempArray,",")) 
        If val(striptonum(element))=val(striptonum(arraylast(tempArray,",")))+1
            If arraylast(tempArray,",") beginswith "-"
                tempArray=arrayrange(tempArray,1,-2,",")+",-"+element
            else
                tempArray=tempArray+",-"+element
            endif
        else
             tempArray=?(tempArray="",element,tempArray+","+element)
        endif
    else 
        tempArray=?(tempArray="",element,tempArray+","+element)
    endif
endloop
earTags=replace(tempArray,",-","-")

Seems to work for your small sample anyway. :relieved:

3

Thanks Gary (I thought it would be either you or Dave who responded) - I’ll give it a road test and let you know.

4

I can’t fault it - many thanks Gary.

5

Grouping is a feature I’ve wanted in arrays for some time, along with an ability to count or total an element.

Consider (the greatly simplified example) array of:

Apples 6
Oranges 4
Apples 2 
Pears 7

What if I want to know how many times each fruit is listed in the array? ie: Apples = 2. Or how many of each fruit I have? ie: Apples = 8.

Yes, there are workarounds and I’ve used them. But I’m currently trying to work with two arrays each containing hundreds of items and dollar amounts, then finding all like items and calculating the amount.

6

Grouping is a great advantage of the data sheet, and at some point, I do not try to reinvent the wheel. So, in similar cases, I export my array as a text file, import it into an intermediate database, do all kinds of grouping and analyzing there, and reimport the analyzed data as an array back into my original database.