Page tree

Trifacta Dataprep


Contents:

On April 28, 2021, Google changed the required permissions for attaching IAM roles to service accounts. If you are using IAM roles for your Google service accounts, please see Changes to User Management.

   

This example illustrates how to use the conditional ranking functions KTHLARGESTIF and KTHLARGESTUNIQUEIF in your recipes.

Source:

Here is some example weather data:

datecityrain_cmtemp_Cwind_mph
1/23/17Valleyville0.0012.88.8
1/23/17Center Town0.319.45.3
1/23/17Magic Mountain0.000.07.3
1/24/17Valleyville0.2517.23.3
1/24/17Center Town0.541.17.6
1/24/17Magic Mountain0.325.08.8
1/25/17Valleyville0.023.36.8
1/25/17Center Town0.833.35.1
1/25/17Magic Mountain0.59-1.76.4
1/26/17Valleyville1.0815.04.2
1/26/17Center Town0.966.17.6
1/26/17Magic Mountain0.77-3.93.0
1/27/17Valleyville1.007.22.8
1/27/17Center Town1.3220.00.2
1/27/17Magic Mountain0.775.65.2
1/28/17Valleyville0.12-6.15.1
1/28/17Center Town0.145.04.9
1/28/17Magic Mountain1.501.10.4
1/29/17Valleyville0.3613.37.3
1/29/17Center Town0.756.19.0
1/29/17Magic Mountain0.603.36.0


Transformation:

In this case, you want to find out the second-most measures for rain, temperature, and wind in Center Town for the week.

Transformation Name Pivot columns
Parameter: Values KTHLARGESTIF(rain_cm,2,city == 'Center Town')
Parameter: Max number of columns to create 1

You can see in the preview that the value is 1.32. Before adding it to your recipe, you change the step to the following:

Transformation Name Pivot columns
Parameter: Values KTHLARGESTIF(temp_C,2,city == 'Center Town')
Parameter: Max number of columns to create 1

The value is 20.

For wind, you modify it to be the following, capturing the third-ranked value:

Transformation Name Pivot columns
Parameter: Values KTHLARGESTIF(wind_mph,3,city == 'Center Town')
Parameter: Max number of columns to create 1

In the results, you notice that there are two values for 8.8. So you change the function to use the KTHLARGESTUNIQUEIF function instead:

Transformation Name Pivot columns
Parameter: Values KTHLARGESTUNIQUEIF(wind_mph,3,city == 'Center Town')
Parameter: Max number of columns to create 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 4, the results would return a different unique ranked value (7.3). 

Results:

You can choose to add any of these steps to generate an aggregated result. As an alternative, you can use a derive transform to insert these calculated results into new columns.

This page has no comments.