Date: Sun, 9 May 2021 02:28:00 +0000 (GMT) Message-ID: <14052606.5989.1620527280293@6a789edf488b> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_5988_291717751.1620527280293" ------=_Part_5988_291717751.1620527280293 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - ARRAYLEN and ARRAYELEMENTAT Functions

# EXAMPLE - ARRAYLEN and ARRAYELEMENTAT Functions

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 in= dex parameter. See ARRAYE= LEMENTAT Function.
• `ARRAYSORT` - Returns array sorted in ascending or descendin= g order. See ARRAYSORT Fu= nction.

Source:

Here are some student test scores. Individual scores are stored in the <= code>Scores column. You want to:

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

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 scor= e:

=20

set column: Scores value: ARRAYSORT(Scores, 'desc= ending')

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

=20

derive type:single value:ARRAYELEMENTAT(Scores,0)= as:'highestScore'

=20

derive type:single value:ARRAYELEMENTAT(Scores,3)= as:'lowestScore'

Tip: You could also generate the `Error` col= umn when the `Scores4` column contains a null value. If no value= exists in the array for the `ARRAYELEMENTAT` function, a null v= alue is returned, which would indicate in this case an insufficient number = of elements (test scores).

You can now track change in test scores:

=20

derive type:single value:SUBTRACT(highestScore,lo= westScore) as:'Score_range'

Results:

=20 =20 =20 =20 =20 =20 =20 =20
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
------=_Part_5988_291717751.1620527280293--