Date: Sun, 18 Apr 2021 06:15:00 +0000 (GMT) Message-ID: <209325877.127912.1618726500615@df68ed866f50> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_127911_1687025238.1618726500615" ------=_Part_127911_1687025238.1618726500615 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html KTHLARGESTIF Function

# KTHLARGESTIF Function

Contents:

=20

=20
=20
=20

=20
=20 Extracts the ranked value from the values in a column, where  `k=3D1`  returns the maximum value, when a specified condition is met. T= he value for  `k`  must be between 1 and 1000, inclusive.  `KTHLARGESTIF` calculations are filtered by a conditi= onal applied to the group.

For purposes of this calculation, two instances of the same value = are treated as separate values. So, if your dataset contains three rows wit= h column values `10````9, and 9, then KTHL= ARGEST returns 9 for&= nbsp;k=3D2 and k=3D3.```

``` Input column can be of Integer or Decimal type. Non-numeric data in the = column is ignored. If a row contains a missing or null value, it is no= t factored into the calculation. NOTE: When added to a transform, this function is appli= ed to the current sample. If you change your sample or run the job, the com= puted values for this function are updated. Transforms that change the numb= er of rows in subsequent recipe steps do not affect the values computed for= this step. To perform a simple kth largest calculation without conditionals, use th= e KTHLARGEST function. See KTHLARGEST Function. Basic Us= age =20 pivot value: KTHLARGESTIF(POS_Sales, 1, DayOfWeek= =3D=3D 'Saturday') group:storeNbr limit:1 Output: Generates a two-column table containing the uni= que values for storeNbr and the top value (rank=3D1) from= the POS_Sales column for each storeNbr = ;when the DayOfWeek value is Saturday. = The limit parameter defines the maximum number of ou= tput columns. Syntax and Arguments =20 pivot value:LISTIF(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. Colu= mn must be a numeric (Integer or Decimal) type. Usage Notes: Required? Data Type Example Value Yes String that corresponds to the name of the colum= n myValues k_integer Integer representing the 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') Example= s Tip: For additional examples, see Common Tasks. Example - Second-most measurements for a specific city This example illustrates how to use the conditional ranking function&nbs= p;KTHLARGESTIF in your recipes. Source: Here is some example weather data: date city rain_cm temp_C wind_mph 1/23/17 Valleyville 0.00 12.8 6.7 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 Transform: In this case, you want to find out the second-most measures for rain, te= mperature, and wind in Center Town for the week.  pivot value:KTHLARGESTIF(rain_cm,2,city =3D=3D 'C= enter Town') limit:1You can see in the preview that the value is=   1.32. Before adding it to your recipe, you change the step to = the following:=20 pivot value:KTHLARGESTIF(temp_C,2,city =3D= =3D 'Center Town') limit:1The value is  20. For wind, you modify it to be the following:=20 pivot value:KTHLARGESTIF(wind_mph,2,city = =3D=3D 'Center Town') limit:1 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 i= nsert these calculated results into new columns.   =20 See Also:=20 =20 =20 =20 Page:= =20 =20 =20 LIST Function= =20 =20 =20 =20 Page:= =20 =20 =20 MAX Function=20 =20 =20 =20 Page:= =20 =20 =20 MIN Function=20 =20 =20 =20 Page:= =20 =20 =20 COUNT Function=20 =20 =20 =20 Page:= =20 =20 =20 COUNTIF Functio= n=20 =20 =20 =20 Page:= =20 =20 =20 Enriching Data=20 =20 =20 =20 Page:= =20 =20 =20 MODE Function= =20 =20 =20 =20 Page:= =20 =20 =20 VAR Function=20 =20 =20 =20 Page:= =20 =20 =20 MODEIF Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 LISTIF Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 KTHLARGEST= IF Function=20 =20 =20 =20 Page:= =20 =20 =20 MAXIF Function=20 =20 =20 =20 Page:= =20 =20 =20 AVERAGE Functio= n=20 =20 =20 =20 Page:= =20 =20 =20 ANY Function=20 =20 =20 =20 Page:= =20 =20 =20 ANYIF Function=20 =20 =20 =20 =20   ```
``` ------=_Part_127911_1687025238.1618726500615-- ```