Page tree

Trifacta Dataprep



Contents:

   

This example describes how to use rolling functions for Datetime values.

Functions:

ItemDescription
ROLLINGMINDATE Function Computes the rolling minimum of Date values forward or backward of the current row within the specified column. Inputs must be of Datetime type.
ROLLINGMAXDATE Function Computes the rolling maximum of date values forward or backward of the current row within the specified column. Inputs must be of Datetime type.
ROLLINGMODEDATE Function Computes the rolling mode (most common value) forward or backward of the current row within the specified column. Input values must be of Datetime data type.

Source:

The following table contains an unordered list of orders:

myDateprodIdorderDollars
2020-03-13p0011445
2020-03-06p002712
2020-03-16p0031374
2020-03-23p0011675
2020-04-09p0021005
2020-08-09p003984
2020-05-02p0011395
2020-06-14p0021866
2020-07-16p003824
2020-09-02p0011785
2020-08-31p002697
2020-10-22p0031513
2020-03-17p001768
2020-03-21p0021893
2020-03-23p0031122
2020-04-06p001805
2020-05-09p0021752
2021-01-09p003616
2020-08-18p0011563
2020-09-12p002730
2020-10-04p003587
2021-02-15p0011979
2021-02-22p002134
2021-03-14p003938


Transformation:

You can use the following Window transformation to calculate the rolling minimum, maximum, and mode dates for the last five orders for each product identifier:

Transformation Name Window
Parameter: Formula1 ROLLINGMINDATE(orderDate, 4, 0)
Parameter: Formula2 ROLLINGMAXDATE(orderDate, 4, 0)
Parameter: Formula3 ROLLINGMODEDATE(orderDate, 4, 0)
Parameter: Group by prodId
Parameter: Order by prodId

You can use the following transformation to rename the generated window columns:

Transformation Name Rename columns
Parameter: Option Manual rename
Parameter: Column window1
Parameter: New column name rollingMinDate
Parameter: Parameter: Column window2
Parameter: New column name rollingMaxDate
Parameter: Parameter: Column window3
Parameter: New column name rollingModeDate

Results:

orderDateprodIdorderDollarsrollingMinDaterollingMaxDaterollingModeDate
3/16/20p00313743/16/203/16/203/16/20
8/9/20p0039843/16/208/9/203/16/20
7/16/20p0038243/16/208/9/203/16/20
10/22/20p00315133/16/2010/22/203/16/20
3/23/20p00311223/16/2010/22/203/16/20
1/9/21p0036163/23/201/9/213/23/20
10/4/20p0035873/23/201/9/213/23/20
3/14/21p0039383/23/203/14/213/23/20
3/13/20p00114453/13/203/13/203/13/20
3/23/20p00116753/13/203/23/203/13/20
5/2/20p00113953/13/205/2/203/13/20
9/2/20p00117853/13/209/2/203/13/20
3/17/20p0017683/13/209/2/203/13/20
4/6/20p0018053/17/209/2/203/17/20
8/18/20p00115633/17/209/2/203/17/20
2/15/21p00119793/17/202/15/213/17/20
3/6/20p0027123/6/203/6/203/6/20
4/9/20p00210053/6/204/9/203/6/20
6/14/20p00218663/6/206/14/203/6/20
8/31/20p0026973/6/208/31/203/6/20
3/21/20p00218933/6/208/31/203/6/20
5/9/20p00217523/21/208/31/203/21/20
9/12/20p0027303/21/209/12/203/21/20
2/22/21p0021343/21/202/22/213/21/20

See Also for EXAMPLE - Rolling Date Functions:

This page has no comments.