## Key

• This line was removed.
• Formatting was changed.
Excerpt

This example

...

 illustrates how to convert the index value of an array for

...

 a specified value

...

 searching from left to right

...

 and right to left by using ARRAYINDEXOF and ARRAYRIGTHINDEXOF functions.

Functions:

D generate list excerpts
pages ARRAYINDEXOF Function,ARRAYRIGHTINDEXOF Function

Source:

The following set of arrays contain results, in order, of a series of races. From this list, the goal is to generate the score for each racer according to the following scoring matrix.

PlacePoints
1st30
2nd20
3rd10
Last-10
Did Not Finish (DNF)-20

Results:

RaceIdRaceResults
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"]

TransformTransformation:

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:

d-

...

trans
RawWrangle true 'countRacers' step derive type: single value: ARRAYLEN(RaceResults) as: 'countRacers' Formula type Single row formula Formula ARRAYLEN(RaceResults) New column name New formula

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

d-

...

trans
RawWrangle true 'arrL-IndexRacer1' step derive type: single value: ARRAYINDEXOF(RaceResults, 'racer1') as: 'arrL-IndexRacer1' Formula type

...

p01Value Single row formula Formula ARRAYINDEXOF(RaceResults, 'racer1') New column name New formula

D trans
RawWrangle true 'arrR-IndexRacer1' step derive type: single value: ARRAYRIGHTINDEXOF(RaceResults, 'racer1') as: 'arrR-IndexRacer1' Formula type Single row formula Formula ARRAYRIGHTINDEXOF(RaceResults, 'racer1') New column name New formula

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

...

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:

d

...

-trans
RawWrangle true (3 - {arrayL-IndexRacer1}) * 10 case condition: ifThenElse if: {arrayL-IndexRacer1} < 3 then: (3 - {arrayL-IndexRacer1}) * 10 else: 0 as: 'ptsRacer1' Condition type Then 0 Conditional column step New column name if...then...else If {arrayL-IndexRacer1} < 3 'ptsRacer1' Else

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

d-

...

trans
RawWrangle true step set col: ptsRacer1 value: IF(ISNULL({arrayL-IndexRacer1}), -20, ptsRacer1)) Columns ptsRacer1 Formula IF(ISNULL({arrayL-IndexRacer1}), -20, ptsRacer1)) Edit column with formula

You can use the following to determine if the specified racer was last in the event:

d-

...

trans
RawWrangle true step set col: ptsRacer1 value: IF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1) Columns ptsRacer1 Formula IF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1) Edit column with formula

Results:

RaceIdRaceResultscountRacersarrR-IndexRacer1arrL-IndexRacer1ptsRacer1
1["racer3","racer5","racer2","racer1","racer6"]5330
2["racer6","racer4","racer2","racer1","racer3","racer5"]6330
3["racer4","racer3","racer5","racer2","racer6","racer1"]655-10
4["racer1","racer2","racer3","racer5"]40020
5["racer5","racer2","racer4","racer6","racer3"]5nullnull-20

D s also
label example_arrayindexof_and_arrayrightindexof_functions