Page tree

Versions Compared

Key

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

...

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


TransformationTransform:

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-

...

Code Block

derive type: single value: ARRAYLEN(RaceResults) as: 'countRacers'

...

p02NameFormulap02ValueARRAYLEN(RaceResults)p03NameNew column nameSearchTermNew formulaCreate columns containing the index values for each racer. Below is the example for racer1:

d-

...

Code Block

derive type: single value: ARRAYINDEXOF(RaceResults, 'racer1') as: 'arrL-IndexRacer1

...

'

...

...


...

D code

derive type: single value: ARRAYRIGHTINDEXOF(RaceResults, 'racer1') as: 'arrR-IndexRacer1'

...

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-

...

Code Block

case condition: ifThenElse if: {arrayL-IndexRacer1} < 3 then: (3 - {arrayL-IndexRacer1}) * 10 else: 0 as: 'ptsRacer1'

...

New column namep01Valueif...then...elsep02NameIfp02Value{arrayL-IndexRacer1} < 3p05Value'ptsRacer1'p04NameElseThe following transform then edits the ptsRacer1 to evaluate for the Did Not Finish (DNF) and last place conditions:

d-

...

Code Block

set col: ptsRacer1 value:

...

IF(ISNULL({arrayL-IndexRacer1}), -20, ptsRacer1))

...

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

d-

...

Code Block

set col: ptsRacer1 value: IF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1)



p01NameColumnsp01ValueptsRacer1p02NameFormulap02ValueIF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1)SearchTermEdit column with formulaResults:

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
labelarrayindexof