Date: Mon, 20 Sep 2021 14:07:27 +0000 (GMT) Message-ID: <1665121419.3913.1632146847660@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_3912_907155200.1632146847660" ------=_Part_3912_907155200.1632146847660 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - KTHLARGESTIF Function

# EXAMPLE - KTHLARGESTIF Function

This example illustrates how to use the conditional ranking func= tions `KTHLARGESTIF` and ```KTHLARGESTUNIQUEIF= ``` in your recipes.

Source:

Here is some example weather data:

=20 =20 =20 =20 =20 =20
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, te= mperature, and wind in Center Town for the week.

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name `Pivot columns` ```KTHLARGESTIF(rain_cm,2,city =3D=3D 'Center= Town')``` `1`
=20

You can see in the preview that the value is `1.32`. Before a= dding it to your recipe, you change the step to the following:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name `Pivot columns` ```KTHLARGESTIF(temp_C,2,city =3D=3D 'Center = Town')``` `1`
=20

The value is `20`.

For wind, you modify it to be the following, capturing the third-ranked = value:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name `Pivot columns` ```KTHLARGESTIF(wind_mph,3,city =3D=3D 'Cente= r Town')``` `1`
=20

In the results, you notice that there are two values for ```8.8<= /code>. So you change the function to use the KTHLARGESTUNIQUEIF=  function instead:```

``` =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20 Transformation NamePivot columnsParameter: ValuesKTHLARGESTUNIQUEIF(wind_mph,3,city =3D=3D = 'Center Town')Parameter: Max number of columns to create1 =20 The result value is 7.6. Note that this value appears = twice, so if you change the rank parameter in the above transformation to&n= bsp;4, the results would return a different unique ranked valu= e (7.3).  Results: You can choose to add any of these steps to generate an aggregated resul= t. As an alternative, you can use a derive transform to insert= these calculated results into new columns. ```
``` ------=_Part_3912_907155200.1632146847660-- ```