Page tree

Release 5.1




Computes the 0-based index value for an array element in the specified column, array literal, or function that returns an array.
  • This function calculates based on the outer layer of an array. If your array is nested, the count of inner elements is not factored.
  • If a row contains a missing array, the returned value is 0. If it contains a value that is not recognized as an array, the returned value is null.

Basic Usage

Array literal reference example:

derive type:single value:ARRAYELEMENTAT([A,B,C,D],2)

Output: Returns the third value in the array, which is C

Column reference example:

derive type:single value:ARRAYELEMENTAT(myArrays,9) as:'10th_myArrays'

Output: Generates the new 10th_myArrays column containing the tenth element of the arrays listed in the myArrays column.

Array function example:

derive type:single value:ARRAYELEMENTAT(CONCAT([colA,colB]),3) as:'strArrayElementAtIndex'

Output: Generates the new  strArrayElementAtIndex  column containing the fourth element of the concatenated array

Syntax and Arguments

derive type:single value:ARRAYELEMENTAT(array_ref,int_index_ref)

ArgumentRequired?Data TypeDescription
array_refYstringName of Array column, Array literal, or function returning an Array to apply to the function
int_index_refYinteger (non-negative)Index value for the array element to return

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


Name of the array column, array literal, or function returning an array whose elements you want to return.

  • Multiple columns and wildcards are not supported.

Usage Notes:


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


Non-negative integer value representing the index value of the array element to return.

  • Value must a non-negative integer. If the value is 0, then the first element of the array is returned.
  • If this value is greater than the length of the string, then a null value is returned.
  • References to columns of integer data type are not supported.

Usage Notes:

Data Type
Example Value
YesInteger (non-negative)5


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 change in scores between first and fourth tests.
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 transforms extracts the first and last value in each student's test scores, provided that they took four tests:

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

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

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(Scores4,Scores1) as:'Scores_change'


AllenAmanda[79, 83,87,81] 79812
BellBobby[85, 92, 94, 98] 859813
DudleyDanny[82,88,81,77] 8277-5
EllisEvan[91,93,87,93] 91932

This page has no comments.