Skip to main content

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 k=1 returns the maximum value, when a specified condition is met. The value for k must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.

KTHLARGESTUNIQUEIF Function

Extracts the ranked unique value from the values in a column, where k=1 returns the maximum value, when a specified condition is met. The value for k must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.

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

Pivot columns

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

Pivot columns

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

Pivot columns

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

Pivot columns

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.

In this section: