Page tree

The cloud-based product Trifacta Free is available now! Register for your free account.

 

Contents:


Computes the rolling maximum of date values forward or backward of the current row within the specified column. Inputs must be of Datetime type.
  • If an input value is missing or null, it is not factored in the computation. For example, for the first row in the dataset, the rolling maximum of previous values is undefined.
  • The row from which to extract a value is determined by the order in which the rows are organized based on the order parameter. 

  • If you are working on a randomly generated sample of your dataset, the values that you see for this function might not correspond to the values that are generated on the full dataset during job execution.

  • The function takes a column name and two optional integer parameters that determine the window backward and forward of the current row.
    • The default integer parameter values are -1 and 0, which computes the rolling function from the current row back to the first row of the dataset.
  • This function works with the Window transform. See Window Transform.

For more information on a non-rolling version of this function, see MAXDATE Function.

Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

Column example:

rollingmaxdate(myDate)

Output: Returns the rolling maximum of all values in the myDate column.

Rows before example:

rollingmaxdate(myDate, 3)

Output: Returns the rolling maximum of the current row and the three previous row values in the myDate column.

Rows before and after example:

rollingmaxdate(myDate, 3, 2)

Output: Returns the rolling maximum of the three previous row values, the current row value, and the two rows after the current one in the myDate column.     

Syntax and Arguments

rollingmaxdate(col_ref, rowsBefore_integer, rowsAfter_integer) order: order_col [group: group_col]


ArgumentRequired?Data TypeDescription
col_refYstringName of column whose values are applied to the function
rowsBefore_integerNintegerNumber of rows before the current one to include in the computation
rowsAfter_integerNintegerNumber of rows after the current one to include in the computation

For more information on the order and group parameters, see Window Transform.

For more information on syntax standards, see Language Documentation Syntax Notes.

col_ref

Name of the column whose values are used to compute the function. Inputs must be Datetime values.

Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString (column reference to Datetime values)transactionDate

rowsBefore_integer, rowsAfter_integer

Integers representing the number of rows before or after the current one from which to compute the rolling function, including the current row. For example, if the first value is 5, the current row and the five rows before it are used in the computation. Negative values for k compute the rolling average from rows preceding the current one.

  • rowBefore=0 generates the current row value only.
  • rowBefore=-1  uses all rows preceding the current one.
  • If rowsAfter is not specified, then the value 0 is applied.
  • If a group parameter is applied, then these parameter values should be no more than the maximum number of rows in the groups.

Usage Notes:

Required?Data TypeExample Value
NoInteger4

Examples

Tip: For additional examples, see Common Tasks.

Example - Rolling date functions

This example describes how to use the rolling computational functions:
  • ROLLINGMINDATE - Computes the rolling minimum of Date values forward or backward of the current row within the specified column. Inputs must be of Datetime type. See ROLLINGMINDATE Function.
  • ROLLINGMAXDATE - Computes the rolling maximum of date values forward or backward of the current row within the specified column. Inputs must be of Datetime type. See ROLLINGMAXDATE Function.
  • ROLLINGMODEDATE - 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. See ROLLINGMODEDATE Function.


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

This page has no comments.