This example covers the following functions:
ARRAYLEN
- Returns 1-based number of elements in an array. See ARRAYLEN Function.ARRAYELEMENTAT
- Returns array element based on 0-based index parameter. See ARRAYELEMENTAT Function.ARRAYSORT
- Returns array sorted in ascending or descending order. See ARRAYSORT Function.Source:
Here are some student test scores. Individual scores are stored in the Scores
column. You want to:
LastName | FirstName | Scores |
---|---|---|
Allen | Amanda | [79, 83,87,81] |
Bell | Bobby | [85, 92, 94, 98] |
Charles | Cameron | [88,81,85] |
Dudley | Danny | [82,88,81,77] |
Ellis | Evan | [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 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 |
You can now track change in test scores:
derive type:single value:SUBTRACT(highestScore,lowestScore) as:'Score_range' |
Results:
LastName | FirstName | Scores | Error | lowestScore | highestScore | Score_range |
---|---|---|---|---|---|---|
Allen | Amanda | [87,83,81,79] | 79 | 87 | 8 | |
Bell | Bobby | [98,94,92,85] | 85 | 98 | 13 | |
Charles | Cameron | [88,85,81] | incomplete | 88 | ||
Dudley | Danny | [88,82,81,77] | 77 | 88 | 11 | |
Ellis | Evan | [93,93,91,87] | 87 | 93 | 6 |