| 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.
| Place | Points |
|---|
| 1st | 30 |
| 2nd | 20 |
| 3rd | 10 |
| Last | -10 |
| Did Not Finish (DNF) | -20 |
Results:
| 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:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'countRacers' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: ARRAYLEN(RaceResults) as: 'countRacers' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ARRAYLEN(RaceResults) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Create columns containing the index values for each racer. Below is the example for racer1:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'arrL-IndexRacer1' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: ARRAYINDEXOF(RaceResults, 'racer1') as: 'arrL-IndexRacer1' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ARRAYINDEXOF(RaceResults, 'racer1') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'arrR-IndexRacer1' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: ARRAYRIGHTINDEXOF(RaceResults, 'racer1') as: 'arrR-IndexRacer1' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | ARRAYRIGHTINDEXOF(RaceResults, 'racer1') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New 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 |
|---|
| RawWrangle | true |
|---|
| p03Value | (3 - {arrayL-IndexRacer1}) * 10 |
|---|
| WrangleText | case condition: ifThenElse if: {arrayL-IndexRacer1} < 3 then: (3 - {arrayL-IndexRacer1}) * 10 else: 0 as: 'ptsRacer1' |
|---|
| p01Name | Condition type |
|---|
| p03Name | Then |
|---|
| p04Value | 0 |
|---|
| SearchTerm | Conditional column |
|---|
| Type | step |
|---|
| p05Name | New column name |
|---|
| p01Value | if...then...else |
|---|
| p02Name | If |
|---|
| p02Value | {arrayL-IndexRacer1} < 3 |
|---|
| p05Value | 'ptsRacer1' |
|---|
| p04Name | Else |
|---|
|
The following transform then edits the ptsRacer1 to evaluate for the Did Not Finish (DNF) and last place conditions:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | set col: ptsRacer1 value: IF(ISNULL({arrayL-IndexRacer1}), -20, ptsRacer1)) |
|---|
| p01Name | Columns |
|---|
| p01Value | ptsRacer1 |
|---|
| p02Name | Formula |
|---|
| p02Value | IF(ISNULL({arrayL-IndexRacer1}), -20, ptsRacer1)) |
|---|
| SearchTerm | Edit column with formula |
|---|
|
You can use the following to determine if the specified racer was last in the event:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | set col: ptsRacer1 value: IF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1) |
|---|
| p01Name | Columns |
|---|
| p01Value | ptsRacer1 |
|---|
| p02Name | Formula |
|---|
| p02Value | IF(arrR-IndexRacer1 == countRacers, -10, ptsRacer1) |
|---|
| SearchTerm | Edit column with formula |
|---|
|
Results:
| RaceId | RaceResults | countRacers | arrR-IndexRacer1 | arrL-IndexRacer1 | ptsRacer1 |
|---|
| 1 | ["racer3","racer5","racer2","racer1","racer6"] | 5 | 3 | 3 | 0 |
| 2 | ["racer6","racer4","racer2","racer1","racer3","racer5"] | 6 | 3 | 3 | 0 |
| 3 | ["racer4","racer3","racer5","racer2","racer6","racer1"] | 6 | 5 | 5 | -10 |
| 4 | ["racer1","racer2","racer3","racer5"] | 4 | 0 | 0 | 20 |
| 5 | ["racer5","racer2","racer4","racer6","racer3"] | 5 | null | null | -20 |
| D s also |
|---|
| label | example_arrayindexof_and_arrayrightindexof_functions |
|---|
|
