EXAMPLE

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:

Transformation:

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

D trans

RawWrangle	true
p03Value	'Error'
Type	step
WrangleText	derive type:single value:IF(ARRAYLEN(Scores) < 4,"incomplete","") as:'Error'
p01Name	Formula type
p01Value	Single row formula
p02Name	Formula
p02Value	IF(ARRAYLEN(Scores) < 4,"incomplete","")
p03Name	New column name
SearchTerm	New formula

This test flags Cameron Charles only.

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

D trans

RawWrangle	true
Type	step
WrangleText	set column: Scores value: ARRAYSORT(Scores, 'descending')
p01Name	Columns
p01Value	Scores
p02Name	Formula
p02Value	ARRAYSORT(Scores, 'descending')
SearchTerm	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
p03Value	'highestScore'
Type	step
WrangleText	derive type:single value:ARRAYELEMENTAT(Scores,0) as:'highestScore'
p01Name	Formula type
p01Value	Single row formula
p02Name	Formula
p02Value	ARRAYELEMENTAT(Scores,0)
p03Name	New column name
SearchTerm	New formula

D trans

RawWrangle	true
p03Value	'lowestScore'
Type	step
WrangleText	derive type:single value:ARRAYELEMENTAT(Scores,3) as:'lowestScore'
p01Name	Formula type
p01Value	Single row formula
p02Name	Formula
p02Value	ARRAYELEMENTAT(Scores,3)
p03Name	New column name
SearchTerm	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
p03Value	'Score_range'
Type	step
WrangleText	derive type:single value:SUBTRACT(highestScore,lowestScore) as:'Score_range'
p01Name	Formula type
p01Value	Single row formula
p02Name	Formula
p02Value	SUBTRACT(highestScore,lowestScore)
p03Name	New column name
SearchTerm	New formula

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

Versions Compared