Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r093
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
pagesARRAYINDEXOF 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"]


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:

D trans
RawWrangletrue
p03Value'countRacers'
Typestep
WrangleTextderive type: single value: ARRAYLEN(RaceResults) as: 'countRacers'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueARRAYLEN(RaceResults)
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03Value'arrL-IndexRacer1'
Typestep
WrangleTextderive type: single value: ARRAYINDEXOF(RaceResults, 'racer1') as: 'arrL-IndexRacer1'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueARRAYINDEXOF(RaceResults, 'racer1')
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'arrR-IndexRacer1'
Typestep
WrangleTextderive type: single value: ARRAYRIGHTINDEXOF(RaceResults, 'racer1') as: 'arrR-IndexRacer1'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueARRAYRIGHTINDEXOF(RaceResults, 'racer1')
p03NameNew column name
SearchTermNew formula

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

Info

NOTE: If ARRAYINDEXOF and ARRAYRIGHTINDEXOF do not return 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:

D trans
RawWrangletrue
p03Value(3 - {arrayL-IndexRacer1}) * 10
WrangleTextcase condition: ifThenElse if: {arrayL-IndexRacer1} < 3 then: (3 - {arrayL-IndexRacer1}) * 10 else: 0 as: 'ptsRacer1'
p01NameCondition type
p03NameThen
p04Value0
SearchTermConditional column
Typestep
p05NameNew column name
p01Valueif...then...else
p02NameIf
p02Value{arrayL-IndexRacer1} < 3
p05Value'ptsRacer1'
p04NameElse

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

D trans
RawWrangletrue
Typestep
WrangleTextset col: ptsRacer1 value: IF(ISNULL({arrayL-IndexRacer1}), -20, ptsRacer1))
p01NameColumns
p01ValueptsRacer1
p02NameFormula
p02ValueIF(ISNULL({arrayL-IndexRacer1}), -20, ptsRacer1))
SearchTermEdit column with formula

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

D trans
RawWrangletrue
Typestep
WrangleTextset col: ptsRacer1 value: IF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1)
p01NameColumns
p01ValueptsRacer1
p02NameFormula
p02ValueIF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1)
SearchTermEdit 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
labelexample_arrayindexof_and_arrayrightindexof_functions