Date: Fri, 28 Jan 2022 19:12:21 +0000 (GMT) Message-ID: <1699496620.127458.1643397141011@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_127457_85601277.1643397141011" ------=_Part_127457_85601277.1643397141011 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 functions.

Functions:

=20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
KTHLARGESTIF Function Extracts the ranked value from the values= in a column, where k=3D1 returns the m= aximum value, when a specified condition is met. The value for = k must be between 1 and 1000, inclusive. Inpu= ts can be Integer, Decimal, or Datetime.
KTHLARGESTUNIQUEIF Function Extracts the ranked unique value from the= values in a column, where k=3D1 return= s 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:

=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. So you change the function to use the KTHLARGESTUNIQUEIF fu= nction instead:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name Pivot columns KTHLARGESTUNIQUEIF(wind_mph,3,city =3D=3D = 'Center Town') 1
=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.

=20
=20