Sorting a subset of data


#1

I was interested in the question posed by Steve in “Saved Searches” about sorting a subset of the database. Along the way someone suggested using arrays, and Jim pointed out that would be relatively challenging approach for a new user (the understatement of 2019 to date). But I have a solution along those lines and thought a separate post was worthwhile.
As far as I know there is no function or statement to sort a two dimensional array on a column, so I developed a procedure to do that, as a subroutine. The parameters are the variable containing the array and the array column to sort on. It assumes tabs separating the elements on each lien and carriage return to separate each row, suitable for displaying in a text list; of course, one could use other characters and pass those to the subroutine also. It took just six lines:

let lvnewarray=parameter(1)
let lvcolnum=parameter(2)

lvnewarray=arrayfilter(lvnewarray,cr(),{array(import(),lvcolnum,tab())+tab()+import() })
lvnewarray=arraysort(lvnewarray,cr())
lvnewarray=arrayfilter(lvnewarray,cr(),{arraydelete(import(),1,1,tab())})

setparameter 1,lvnewarray

This procedure copies the column you want to sort on to the first position in each line of the array, then sorts the array, then removes the first column, and returns the sorted array to the calling procedure.

With that procedure, one can build an array that contains a subset of the data and then sort that array, and then save that search and retrieve it if needed. Here’s a screen shot of the sample database I constructed to do this.


Anyone who is interested is welcome to a copy.


#2

There is Tom - the arraymultisort statement and its function. There are lots more like that in my CustomArraysAndStrings collection on the Database Exchange site. There are also some useful maths procedures in the CustomMaths collection.


#3

Of course, that is not surprising. I should have thought of that.


#4

Hi Michael, I don’t see any function or statement called arraymultisort in the CustomArraysandStrings that I just downloaded. Maybe it’s in a different database?


#5

http://www.provue.com/panoramax/help/statement_arraymultisort.html


#6

I guess I overlooked that statement!