EXAMPLE - KTHLARGESTIF Function
This example illustrates how to use the conditional ranking functions.
Functions:
Item | Description |
|---|---|
KTHLARGESTIF Function | Extracts the ranked value from the values in a column, where |
KTHLARGESTUNIQUEIF Function | Extracts the ranked unique value from the values in a column, where |
Source:
Here is some example weather data:
date | city | rain_cm | temp_C | wind_mph |
|---|---|---|---|---|
1/23/17 | Valleyville | 0.00 | 12.8 | 8.8 |
1/23/17 | Center Town | 0.31 | 9.4 | 5.3 |
1/23/17 | Magic Mountain | 0.00 | 0.0 | 7.3 |
1/24/17 | Valleyville | 0.25 | 17.2 | 3.3 |
1/24/17 | Center Town | 0.54 | 1.1 | 7.6 |
1/24/17 | Magic Mountain | 0.32 | 5.0 | 8.8 |
1/25/17 | Valleyville | 0.02 | 3.3 | 6.8 |
1/25/17 | Center Town | 0.83 | 3.3 | 5.1 |
1/25/17 | Magic Mountain | 0.59 | -1.7 | 6.4 |
1/26/17 | Valleyville | 1.08 | 15.0 | 4.2 |
1/26/17 | Center Town | 0.96 | 6.1 | 7.6 |
1/26/17 | Magic Mountain | 0.77 | -3.9 | 3.0 |
1/27/17 | Valleyville | 1.00 | 7.2 | 2.8 |
1/27/17 | Center Town | 1.32 | 20.0 | 0.2 |
1/27/17 | Magic Mountain | 0.77 | 5.6 | 5.2 |
1/28/17 | Valleyville | 0.12 | -6.1 | 5.1 |
1/28/17 | Center Town | 0.14 | 5.0 | 4.9 |
1/28/17 | Magic Mountain | 1.50 | 1.1 | 0.4 |
1/29/17 | Valleyville | 0.36 | 13.3 | 7.3 |
1/29/17 | Center Town | 0.75 | 6.1 | 9.0 |
1/29/17 | Magic Mountain | 0.60 | 3.3 | 6.0 |
Transformation:
In this case, you want to find out the second-most measures for rain, temperature, and wind in Center Town for the week.
Transformation Name | |
|---|---|
Parameter: Values | KTHLARGESTIF(rain_cm,2,city == 'Center Town') |
Parameter: Max number of columns to create | 1 |
You can see in the preview that the value is 1.32. Before adding it to your recipe, you change the step to the following:
Transformation Name | |
|---|---|
Parameter: Values | KTHLARGESTIF(temp_C,2,city == 'Center Town') |
Parameter: Max number of columns to create | 1 |
The value is 20.
For wind, you modify it to be the following, capturing the third-ranked value:
Transformation Name | |
|---|---|
Parameter: Values | KTHLARGESTIF(wind_mph,3,city == 'Center Town') |
Parameter: Max number of columns to create | 1 |
In the results, you notice that there are two values for 8.8. So you change the function to use the KTHLARGESTUNIQUEIF function instead:
Transformation Name | |
|---|---|
Parameter: Values | KTHLARGESTUNIQUEIF(wind_mph,3,city == 'Center Town') |
Parameter: Max number of columns to create | 1 |
The result value is 7.6. Note that this value appears twice, so if you change the rank parameter in the above transformation to 4, the results would return a different unique ranked value (7.3).
Results:
You can choose to add any of these steps to generate an aggregated result. As an alternative, you can use a derive transform to insert these calculated results into new columns.