Date: Sun, 16 Jan 2022 18:35:54 +0000 (GMT) Message-ID: <702502638.105176.1642358154367@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_105175_1051088238.1642358154367" ------=_Part_105175_1051088238.1642358154367 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 illustrates how to convert the index value= of an array for a specified value searching from left to right and right t= o left by using ARRAYINDEXOF and ARRAYRIGTHINDEXOF functions.

Functions:

=20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
ARRAYINDEXOF Function Computes the index at which a specified element= is first found within an array. Indexing is left to right.
ARRAYRIGHTINDEXOF Function Computes the index at which a specified element= is first found within an array, when searching right to left. Returned val= ue is based on left-to-right indexing.

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

=20
=20