Date: Sat, 16 Oct 2021 14:31:00 +0000 (GMT) Message-ID: <1131963805.46792.1634394660245@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_46791_287082249.1634394660245" ------=_Part_46791_287082249.1634394660245 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 cond= itional 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 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 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.=20 pivot value:KTHLARGESTIF(rain_cm,2,city =3D=3D 'C= enter Town') limit:1 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 pivot value:KTHLARGESTIF(temp_C,2,city =3D= =3D 'Center Town') limit:1 The value is 20. For wind, you modify it to be the following, capturing the third-ranked = value:=20 pivot value:KTHLARGESTIF(wind_mph,3,city =3D=3D '= Center Town') limit:1 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 pivot value:KTHLARGESTUNIQUEIF(wind_mph,3,city = =3D=3D 'Center Town') limit:1 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 Create Aggre= gations=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 VAR Function=20 =20 =20 =20 Page:= =20 =20 =20 UNIQUE Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 Enriching Data=20 =20 =20 =20 Page:= =20 =20 =20 COUNTIF Functio= n=20 =20 =20 =20 Page:= =20 =20 =20 COUNT Function=20 =20 =20 =20 Page:= =20 =20 =20 MIN Function=20 =20 =20 =20 Page:= =20 =20 =20 MODE Function= =20 =20 =20 =20 Page:= =20 =20 =20 MODEIF Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 Create = Branching Outputs=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 LISTIF Function<= /a>=20 =20 =20 =20 =20   ```
``` ------=_Part_46791_287082249.1634394660245-- ```