Date: Fri, 31 Mar 2023 06:59:21 +0000 (UTC) Message-ID: <145521281.168.1680245961796@3978a672e405> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_167_823350231.1680245961796" ------=_Part_167_823350231.1680245961796 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html KTHLARGESTUNIQUEIF Function

# KTHLARGESTUNIQUEIF Function

=20

Contents:

=20

=20
=20
=20

=20
=20

Extracts the ranked unique value from the values in a column, = where `k=3D1` returns the maximum value, = when a specified condition is met. The 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, = see Wrangle Language. <= /p>

## B= asic Usage

=20

```kthlargestuniqueif(POS_Sales, 2, DayOfWeek =3D=3D= &apos;Saturday&apos;)```

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_expressio= n) [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:

=20

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:

=20

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

## = Examples

=20
=20
=20

=20

### 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 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 =20 See Also for KTHLARGESTUNIQUEIF Function:=20 =20 =20 Page:<= /span>=20 =20 KTHLARGEST Function=20 =20 =20 Page:<= /span>=20 =20 KTHLARGESTIF Function=20 =20 =20 Page:<= /span>=20 =20 KTHLARGESTUNIQUE Function=20 =20 =20 Page:<= /span>=20 =20 LIST Function=20 =20 =20 Page:<= /span>=20 =20 LISTIF Function=20 =20 =20 Page:<= /span>=20 =20 AVERAGE Function=20 =20 =20 Page:<= /span>=20 =20 COUNTA Function=20 =20 =20 Page:<= /span>=20 =20 VAR Function=20 =20 =20 Page:<= /span>=20 =20 MODE Function=20 =20 =20 Page:<= /span>=20 =20 PERCENTILE Function=20 =20 =20 =20 =20 =20   ```
``` ------=_Part_167_823350231.1680245961796-- ```