Date: Sat, 29 Jan 2022 00:57:49 +0000 (GMT) Message-ID: <1899698465.127494.1643417869929@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_127493_2046756423.1643417869929" ------=_Part_127493_2046756423.1643417869929 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:

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]

Transformation:

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula IF(ARRAYLEN(Scores) < 4,"incomplete",""= ) 'Error'
=20

This test flags Cameron Charles only.

The following transform sorts the array values in highest to lowest scor= e:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Edit column with formula Scores ARRAYSORT(Scores, 'descending')=20
=20

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
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula ARRAYELEMENTAT(Scores,0) 'highestScore'
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula ARRAYELEMENTAT(Scores,3) 'lowestScore'
=20

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
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula SUBTRACT(highestScore,lowestScore)<= /td>=20 'Score_range'
=20

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_127493_2046756423.1643417869929--