Page tree

Release 5.1


Contents:

   

Contents:


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.

array_ref

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

int_index_ref

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:

Required?
Data Type
Example Value
YesInteger (non-negative)5


Examples


Tip: For additional examples, see Common Tasks.

Example - Student progress across tests

This example covers the following functions:

Source:

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.
LastNameFirstNameScores
AllenAmanda[79, 83,87,81]
BellBobby[85, 92, 94, 98]
CharlesCameron[88,81,85]
DudleyDanny[82,88,81,77]
EllisEvan[91,93,87,93]

Transform:

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'


Results:

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

This page has no comments.