Date: Mon, 20 Mar 2023 13:53:14 +0000 (UTC) Message-ID: <1498758374.79167.1679320394477@1323b43bed1d> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_79166_261641651.1679320394476" ------=_Part_79166_261641651.1679320394476 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - ARRAYINDEXOF and ARRAYRIGHTINDEXOF Functions

# EXAMPLE - ARRAYINDEXOF and ARRAYRIGHTINDEXOF Functions

This example covers the following functions:

• `ARRAYINDEXOF` - Returns the index value of an array for the= specified value, searching from left to right. See ARRAYINDEXOF Function.
• `ARRAYRIGHTINDEXOF` - Returns the index value of an array fo= r the specified value, searching from right to left. See ARRAYRIGHTINDEXOF Function.&nbs= p;

Source:

The following set of arrays contain results, in order, of a series of ra= ces. From this list, the goal is to generate the score for each racer accor= ding to the following scoring matrix.

=20 =20 =20
Place Points
1st 30
2nd 20
3rd 10
Last -10
Did Not Finish (DNF) -20

Results:

=20 =20 =20
RaceId RaceResults
1
["racer3","racer5","racer2","racer1","racer6"]
2
["racer6","racer4","racer2","racer1","racer3","racer5"]
3
["racer4","racer3","racer5","racer2","racer6","racer1"]
4
["racer1","racer2","racer3","racer5"]
5
["racer5","racer2","racer4","racer6","racer3"]

Transformation:

Note that the number of racers varies with each race, so determining the= position of the last racer depends on the number in the event. The number = of racers can be captured using the following:

=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` `ARRAYLEN(RaceResults)` `'countRacers'`
=20

Create columns containing the index values for each racer. Below is the = example for `racer1`:

=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` `ARRAYINDEXOF(RaceResults, 'racer1')`= `'arrL-IndexRacer1'`
=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` `ARRAYRIGHTINDEXOF(RaceResults, 'racer1')` `'arrR-IndexRacer1'`
=20

You can then compare the values in the two columns to determine if they = are the same.

NOTE: If ARRAYINDEXOF and ARRAYRIGHTINDEXOF do not retu= rn the same value for the same inputs, then the value is not unique in the = array.

Since the points awarded for 1st, 2nd, and 3rd place follow a consistent= pattern, you can use the following single statement to compute points for = podium finishes for `racer1`: computing based on the value = stored for the left index value:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Conditional column` `if...then...else` `{arrayL-IndexRacer1} < 3` `(3 - {arrayL-IndexRacer1}) * 10`=20 `0` `'ptsRacer1'`
=20

The following transform then edits the `ptsRacer1` to ev= aluate for the Did Not Finish (DNF) and last place conditions:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Edit column with formula` `ptsRacer1` ```IF(ISNULL({arrayL-IndexRacer1}), -20, ptsR= acer1))```
=20

You can use the following to determine if the specified racer was last i= n the event:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Edit column with formula` `ptsRacer1` ```IF(arrR-IndexRacer1 =3D=3D countRacers, -1= 0, ptsRacer1)```
=20

Results:

=20 =20 =20 =20 =20 =20 =20
RaceId RaceResults countRacers arrR-IndexRacer1 arrL-IndexRacer1 ptsRacer1
1 ["racer3","racer5","racer2","racer1","racer6"] 5 3 3 0
2 ["racer6","racer4","racer2","racer1","racer3","r= acer5"] 6 3 3 0
3 ["racer4","racer3","racer5","racer2","racer6","r= acer1"] 6 5 5 -10
4 ["racer1","racer2","racer3","racer5"] 4 0 0 20
5 ["racer5","racer2","racer4","racer6","racer3"] 5 null null -20

------=_Part_79166_261641651.1679320394476--