Key

• This line was removed.
• Formatting was changed.

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:

1. Flag the students who have not taken four tests.
2. Compute the range in scores for each student.
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]

Transformation:

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

D trans
RawWrangle true 'Error' step derive type:single value:IF(ARRAYLEN(Scores) < 4,"incomplete","") as:'Error' Formula type Single row formula Formula IF(ARRAYLEN(Scores) < 4,"incomplete","") New column name New formula

This test flags Cameron Charles only.

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

D trans
RawWrangle true step set column: Scores value: ARRAYSORT(Scores, 'descending') Columns Scores Formula ARRAYSORT(Scores, 'descending') Edit column with formula

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

D trans
RawWrangle true 'highestScore' step derive type:single value:ARRAYELEMENTAT(Scores,0) as:'highestScore' Formula type Single row formula Formula ARRAYELEMENTAT(Scores,0) New column name New formula

D trans
RawWrangle true 'lowestScore' step derive type:single value:ARRAYELEMENTAT(Scores,3) as:'lowestScore' Formula type Single row formula Formula ARRAYELEMENTAT(Scores,3) New column name New formula

Tip

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:

D trans
RawWrangle true 'Score_range' step derive type:single value:SUBTRACT(highestScore,lowestScore) as:'Score_range' Formula type Single row formula Formula SUBTRACT(highestScore,lowestScore) New column name New formula

Results:

LastNameFirstNameScoresErrorlowestScorehighestScoreScore_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