Page tree

Trifacta SaaS



Contents:

   

This example illustrates how you can apply conditionals to calculate minimum, maximum, and most common date values.

Functions:

ItemDescription
KTHLARGESTDATE Function Extracts the ranked Datetime value from the values in a column, where k=1 returns the maximum value. The value for k must be between 1 and 1000, inclusive. Inputs must be valid Datetime values.
KTHLARGESTUNIQUEDATE Function Extracts the ranked unique Datetime value from the values in a column, where k=1 returns the maximum value. The value for k must be between 1 and 1000, inclusive. Inputs must be Datetime.
KTHLARGESTDATEIF Function Extracts the ranked Datetime value from the values in a column, where k=1 returns the maximum value, when a specified condition is met. The value for k must be between 1 and 1000, inclusive. Inputs must be Datetime.
KTHLARGESTUNIQUEDATEIF Function Extracts the ranked unique Datetime value from the values in a column, where k=1 returns the maximum value, when a specified condition is met. The value for k must be between 1 and 1000, inclusive. Inputs must be Datetime.

Source:

Here is some example transaction data:

DateProductUnitsUnitCostOrderValue
3/28/2020ProductA410.0040.00
3/8/2020ProductB420.0080.00
3/12/2020ProductC230.0060.00
3/23/2020ProductA110.0010.00
3/20/2020ProductB220.0040.00
3/12/2020ProductC930.00270.00
3/28/2020ProductA510.0050.00
3/23/2020ProductB820.00160.00
3/16/2020ProductC930.00270.00
3/8/2020ProductA510.0050.00
3/10/2020ProductB320.0060.00
3/13/2020ProductC130.0030.00
3/12/2020ProductA710.0070.00
3/10/2020ProductB720.00140.00
3/24/2020ProductC930.00270.00
3/15/2020ProductA810.0080.00
3/10/2020ProductB520.00100.00
3/10/2020ProductC430.00120.00


Transformation:

The following transformation computes the third highest date in the Date column:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula kthlargestdate(Date, 3)
Parameter: New column name 'kthlargestdate'

This transformation computes the third highest unique value in the Date column:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula kthlargestuniquedate(Date, 3)
Parameter: New column name 'kthlargestuniquedate'

Following transformation calculates the 3rd highest date value when the OrderValue > 200:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula kthlargestdateif(Date, 3, OrderValue > 200)
Parameter: New column name 'kthlargestdateif'

Following transformation calculates the 3rd highest unique date value when the OrderValue > 200:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula kthlargestuniquedateif(Date, 3, OrderValue > 200)
Parameter: New column name 'kthlargestuniquedateif'

Results: 

DateProductUnitsUnitCostOrderValuekthlargestdatekthlargestuniquedatekthlargestdateifkthlargestuniquedateif
3/28/2020ProductA410.0040.0003-24-202003-23-202003-23-202003-23-2020
3/8/2020ProductB420.0080.0003-24-202003-23-202003-23-202003-23-2020
3/12/2020ProductC230.0060.0003-24-202003-23-202003-23-202003-23-2020
3/23/2020ProductA110.0010.0003-24-202003-23-202003-23-202003-23-2020
3/20/2020ProductB220.0040.0003-24-202003-23-202003-23-202003-23-2020
3/12/2020ProductC930.00270.0003-24-202003-23-202003-23-202003-23-2020
3/28/2020ProductA510.0050.0003-24-202003-23-202003-23-202003-23-2020
3/23/2020ProductB820.00160.0003-24-202003-23-202003-23-202003-23-2020
3/16/2020ProductC930.00270.0003-24-202003-23-202003-23-202003-23-2020
3/8/2020ProductA510.0050.0003-24-202003-23-202003-23-202003-23-2020
3/10/2020ProductB320.0060.0003-24-202003-23-202003-23-202003-23-2020
3/13/2020ProductC130.0030.0003-24-202003-23-202003-23-202003-23-2020
3/12/2020ProductA710.0070.0003-24-202003-23-202003-23-202003-23-2020
3/10/2020ProductB720.00140.0003-24-202003-23-202003-23-202003-23-2020
3/24/2020ProductC930.00270.0003-24-202003-23-202003-23-202003-23-2020
3/15/2020ProductA810.0080.0003-24-202003-23-202003-23-202003-23-2020
3/10/2020ProductB520.00100.0003-24-202003-23-202003-23-202003-23-2020
3/10/2020ProductC430.00120.0003-24-202003-23-202003-23-202003-23-2020


This page has no comments.