Date: Sat, 25 Sep 2021 07:36:53 +0000 (GMT) Message-ID: <351765885.10535.1632555413972@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_10534_1829678496.1632555413972" ------=_Part_10534_1829678496.1632555413972 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&= nbsp; `k=3D1` returns the maximum value, when a specified conditi= on is met. The value for  `k` must be between 1 and 1000, inclusive. Inputs can be In= teger or Decimal.

`KTHLARGESTUNIQUEIF` calculations are filtered by a cond= itional applied to the group.

For purposes of this calculation, two instances of the same value are tr= eated as the same value of `k`. So, if your dataset contain= s four rows with column values `10` , ```9 , 9 , and 8, the the func= tion returns 9 for k=3D2 and&= nbsp;8 for k=3D3.```

``` Input column can be of Integer or Decimal type. Other values column are = ignored. If a row contains a missing or null value, it is not factored 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') Output: Returns = the secondmost value (rank=3D2) from the POS_Sales c= olumn when the DayOfWeek value is Sa= turday. 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 extrac= t 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 or Decimal values. 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<= /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. =20 See Also:=20 =20 =20 =20 Page:= =20 =20 =20 Enriching Data=20 =20 =20 =20 Page:= =20 =20 =20 Create Aggre= gations=20 =20 =20 =20 Page:= =20 =20 =20 ANYIF Function=20 =20 =20 =20 Page:= =20 =20 =20 COUNT Function=20 =20 =20 =20 Page:= =20 =20 =20 COUNTA Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 COUNTAIF Funct= ion=20 =20 =20 =20 Page:= =20 =20 =20 COUNTDI= STINCTIF Function=20 =20 =20 =20 Page:= =20 =20 =20 COUNTIF Functio= n=20 =20 =20 =20 Page:= =20 =20 =20 KTHLARGEST F= unction=20 =20 =20 =20 Page:= =20 =20 =20 KTHLARGEST= IF Function=20 =20 =20 =20 Page:= =20 =20 =20 KTHLAR= GESTUNIQUE Function=20 =20 =20 =20 Page:= =20 =20 =20 LIST Function= =20 =20 =20 =20 Page:= =20 =20 =20 LISTIF Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 MAX Function=20 =20 =20 =20 Page:= =20 =20 =20 MIN Function=20 =20 =20 =20 =20   ```
``` ------=_Part_10534_1829678496.1632555413972-- ```