Date: Thu, 27 Jan 2022 16:16:56 +0000 (GMT) Message-ID: <698028689.127106.1643300216121@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_127105_2063129525.1643300216121" ------=_Part_127105_2063129525.1643300216121 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html KTHLARGESTUNIQUEIF Function

KTHLARGESTUNIQUEIF Function

Contents:

=20

=20
=20
=20

=20
=20

Extracts the ranked unique value from the values in a column, where&n= bsp; k=3D1  returns the maximum value, when a specified condition is met. T= he value for  k  must be between 1 and 1000, inclusive. Inputs can be Inte= ger, Decimal, or Datetime.

KTHLARGESTUNIQUEIF calculations are filtered by = a conditional applied to the group.

For purposes of this calculation, two instances of the same value = are treated as the same value of k. So, if your dataset c= ontains four rows with column values 10 , 9 , 9 , and 8, the th= e function returns 9 for k=3D2&nbs= p;and 8 for k=3D3.

Input column can be of Integer, Decimal or Datetime type. Other values c= olumn are ignored. If a row contains a missing or null value, it is not fac= tored into the calculation.

NOTE: When added to a transformation, this function is = applied to the current sample. If you change your sample or run the job, th= e computed values for this function are updated. Transformations that chang= e the number of rows in subsequent recipe steps do not affect the values co= mputed for this step.

To perform a simple kth largest unique calculation without conditionals,= use the KTHLARGESTUNIQUE function. See KTHLARGESTUNIQUE Function.

Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation language.= Wrangle is not SQL. For more information, se= e Wrangle Language.

Ba= sic Usage

=20

kthlargestuniqueif(POS_Sales, 2, DayOfWeek= =3D=3D 'Saturday')

Ou= tput: Returns the secondmost value (rank=3D2) from the <= code style=3D"letter-spacing: 0.0px;">POS_Sales column when the= DayOfWeek value is Saturday.

Syntax and Arguments

=20

kthlargestuniqueif(col_ref, limit, test_ex= pression) [group:group_col_ref] [limit:limit_count]

Argument Required? Data Type Description
col_ref Y string Reference to the column you wish t= o evaluate.
k_integer Y integer The ranking of the value to = extract from the source column
test_expression Y string Expression that is evaluated. Must resolve to true or false

For more information on syntax standards, see Language Documentation Syntax Note= s.

For more information on the group and limit=  parameter, see Pivot Transform.

col_ref

Name of the column whose values you wish to use in the calculation. = ;Inputs must be Integer, Decimal, or Datetime values.

NOTE: If the input is in Datetime type, the output is i= n unixtime format. You can wrap these outputs in the DATEFORMAT function to= output the results in the appropriate Datetime format. See DATEFORMAT Function.

Usage Notes:

Required? Data Type Example Value
Yes String that corresponds to the name of the colum= n myValues

k_integer

Integer representing the unique ranking of the value to extract from the= source column.

NOTE: The value for k must be an integer b= etween 1 and 1,000 inclusive.

• k=3D1 represents the maximum value in the column.&nbs= p;
• If k is greater than or equal to the number of values in the column, th= e minimum value is returned.
• Missing and null values are not factored into the ranking of k.

test_expression

This parameter contains the expression to evaluate. This expression must= resolve to a Boolean (true or false) v= alue.

Usage Notes:

Required? Data Type Example Value
Yes String expression that evaluates to true or false (LastName =3D=3D 'Mouse' && FirstN= ame =3D=3D 'Mickey')

E= xamples

Example - Second-most measurements for a specific city

This example illustrates how to use the conditional ranking functions&nb= sp;KTHLARGESTIF and KTHLARGESTUNIQUEIF&= nbsp;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. 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