ARRAYINDEXOF Function
Computes the index at which a specified element is first found within an array. Indexing is left to right.
Leftmost index value is
0
.If the element is not found, null is returned.
For righttoleft searching, use ARRAYRIGHTINDEXOF.
If only one element exists in the array, both functions return the same value.
For more information, see ARRAYRIGHTINDEXOF Function.
Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.
Basic Usage
Array literal reference example:
arrayindexof(["A","B","C","D"],"C")
Output: Returns the index of the element "C" in the array, which is 2
in an 0based index.
Column reference example:
arrayindexof([myValues],myElement)
Output: Returns the index in the myValues
arrays for the elements listed in the myElement
column.
Syntax and Arguments
arrayindexof(array_ref,my_element)
Argument  Required?  Data Type  Description 

array_ref  Y  array or string  Name of Array column, Array literal, or function returning an Array to apply to the function 
my_element  Y  any  The element to locate in the array 
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 element you want to locate.
Multiple columns and wildcards are not supported.
Usage Notes:
Required?  Data Type  Example Value 

Yes  String (column reference or function) or array literal  myArray1 
my_element
Element literal that you wish to locate in the array. It can be a value of any data type.
Usage Notes:
Required?  Data Type  Example Value 

Yes  Any  "1st" 
Examples
Tip
For additional examples, see Common Tasks.
Example  Computing points based on position of finish
This example illustrates how to convert the index value of an array for a specified value searching from left to right and right to left by using ARRAYINDEXOF and ARRAYRIGTHINDEXOF functions.
Functions:
Item  Description 

ARRAYINDEXOF Function  Computes the index at which a specified element is first found within an array. Indexing is left to right. 
ARRAYRIGHTINDEXOF Function  Computes the index at which a specified element is first found within an array, when searching right to left. Returned value is based on lefttoright indexing. 
Source:
The following set of arrays contain results, in order, of a series of races. From this list, the goal is to generate the score for each racer according to the following scoring matrix.
Place  Points 

1st  30 
2nd  20 
3rd  10 
Last  10 
Did Not Finish (DNF)  20 
Results:
RaceId  RaceResults 

1  ["racer3","racer5","racer2","racer1","racer6"] 
2  ["racer6","racer4","racer2","racer1","racer3","racer5"] 
3  ["racer4","racer3","racer5","racer2","racer6","racer1"] 
4  ["racer1","racer2","racer3","racer5"] 
5  ["racer5","racer2","racer4","racer6","racer3"] 
Transformation:
Note that the number of racers varies with each race, so determining the position of the last racer depends on the number in the event. The number of racers can be captured using the following:
Transformation Name 


Parameter: Formula type  Single row formula 
Parameter: Formula  ARRAYLEN(RaceResults) 
Parameter: New column name  'countRacers' 
Create columns containing the index values for each racer. Below is the example for racer1
:
Transformation Name 


Parameter: Formula type  Single row formula 
Parameter: Formula  ARRAYINDEXOF(RaceResults, 'racer1') 
Parameter: New column name  'arrLIndexRacer1' 
Transformation Name 


Parameter: Formula type  Single row formula 
Parameter: Formula  ARRAYRIGHTINDEXOF(RaceResults, 'racer1') 
Parameter: New column name  'arrRIndexRacer1' 
You can then compare the values in the two columns to determine if they are the same.
Note
If ARRAYINDEXOF and ARRAYRIGHTINDEXOF do not return the same value for the same inputs, then the value is not unique in the array.
Since the points awarded for 1st, 2nd, and 3rd place follow a consistent pattern, you can use the following single statement to compute points for podium finishes for racer1
: computing based on the value stored for the left index value:
Transformation Name 


Parameter: Condition type  if...then...else 
Parameter: If  {arrayLIndexRacer1} < 3 
Parameter: Then  (3  {arrayLIndexRacer1}) * 10 
Parameter: Else  0 
Parameter: New column name  'ptsRacer1' 
The following transform then edits the ptsRacer1
to evaluate for the Did Not Finish (DNF) and last place conditions:
Transformation Name 


Parameter: Columns  ptsRacer1 
Parameter: Formula  IF(ISNULL({arrayLIndexRacer1}), 20, ptsRacer1)) 
You can use the following to determine if the specified racer was last in the event:
Transformation Name 


Parameter: Columns  ptsRacer1 
Parameter: Formula  IF(arrRIndexRacer1 == countRacers, 10, ptsRacer1) 
Results:
RaceId  RaceResults  countRacers  arrRIndexRacer1  arrLIndexRacer1  ptsRacer1 

1  ["racer3","racer5","racer2","racer1","racer6"]  5  3  3  0 
2  ["racer6","racer4","racer2","racer1","racer3","racer5"]  6  3  3  0 
3  ["racer4","racer3","racer5","racer2","racer6","racer1"]  6  5  5  10 
4  ["racer1","racer2","racer3","racer5"]  4  0  0  20 
5  ["racer5","racer2","racer4","racer6","racer3"]  5  null  null  20 