Page tree

Release 6.0.2




Sorts array values in the specified column, array literal, or function that returns an array in ascending or descending order.
  • This function calculates based on the outer layer of an array. If your array is nested, the sorting of inner elements is not factored.

Basic Usage

Array literal reference example:

derive type:single value:ARRAYSORT([A,B,C,D],descending)

Output: Generates the following array in the new column: [D,C,B,A]

Column reference example:

derive type:single value:ARRAYSORT(myArrays,ascending) as:'myArrays_ascending'

Output: Generates the new myArrays_ascending column containing the arrays listed in the myArrays column sorted in ascending order.

Syntax and Arguments

derive type:single value:ARRAYSORT(array_ref,order_enum)

ArgumentRequired?Data TypeDescription
array_refYstringName of Array column, Array literal, or function returning an Array to apply to the function
order_enumYstring (enumerated value)

Order is defined as either:

  • ascending (default)
  • descending

For more information on syntax standards, see Language Documentation Syntax Notes.


Name of the array column, array literal, or function returning an array whose array values you wish to sort.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString (column reference or function) or array literalmyArray1


String literal indicating the order by which the referenced arrays should be sorted:

  • ascending - (default) lowest values for the valid data type are listed first. 
  • descending - Null/empty values are sorted first, followed by mismatched values. Then, the array values that are valid for the specified data type are listed in descending order.  
  • For more information on the rules of sorting, see Sort Order.

Usage Notes:

Data Type
Example Value
NoString (enumerated value)descending


Tip: For additional examples, see Common Tasks.

Example - Student progress across tests

This example covers the following functions:


Here are some student test scores. Individual scores are stored in the Scores column. You want to:

  1. Flag the students who have not taken four tests.
  2. Compute the range in scores for each student.
AllenAmanda[79, 83,87,81]
BellBobby[85, 92, 94, 98]


First, you want to flag the students who did not take all four tests:

derive type:single value:IF(ARRAYLEN(Scores) < 4,"incomplete","") as:'Error'

This test flags Cameron Charles only.

The following transform sorts the array values in highest to lowest score:

set column: Scores value: ARRAYSORT(Scores, 'descending')

The following transforms extracts the first (highest) and last (lowest) value in each student's test scores, provided that they took four tests:

derive type:single value:ARRAYELEMENTAT(Scores,0) as:'highestScore'

derive type:single value:ARRAYELEMENTAT(Scores,3) as:'lowestScore'

Tip: You could also generate the Error column when the Scores4 column contains a null value. If no value exists in the array for the ARRAYELEMENTAT function, a null value is returned, which would indicate in this case an insufficient number of elements (test scores).

You can now track change in test scores:

derive type:single value:SUBTRACT(highestScore,lowestScore) as:'Score_range'


AllenAmanda[87,83,81,79] 79878
BellBobby[98,94,92,85] 859813
CharlesCameron[88,85,81]incomplete 88 
DudleyDanny[88,82,81,77] 778811
EllisEvan[93,93,91,87] 87936

This page has no comments.