Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r094
Excerpt

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

Functions:

D generate list excerpts
pagesROLLINGMINDATE Function,ROLLINGMAXDATE Function,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:

D trans
RawWrangletrue
p03ValueROLLINGMODEDATE(orderDate, 4, 0)
WrangleTextwindow value: ROLLINGMINDATE(orderDate, 4, 0),ROLLINGMAXDATE(orderDate, 4, 0),ROLLINGMODEDATE(orderDate, 4, 0) group: prodId order: prodId
p01NameFormula1
p03NameFormula3
p04ValueprodId
SearchTermWindow
Typestep
p05NameOrder by
p01ValueROLLINGMINDATE(orderDate, 4, 0)
p02NameFormula2
p02ValueROLLINGMAXDATE(orderDate, 4, 0)
p05ValueprodId
p04NameGroup by

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

D trans
RawWrangletrue
p03ValuerollingMinDate
p06NameParameter: Column
WrangleTextrename type: manual mapping: [window1,'rollingMinDate'],[window2,'rollingMaxDate'],[window3,'rollingModeDate']
p01NameOption
p06Valuewindow3
p03NameNew column name
p07ValuerollingModeDate
p04Valuewindow2
SearchTermRename columns
p07NameNew column name
Typestep
p05NameNew column name
p01ValueManual rename
p02NameColumn
p02Valuewindow1
p05ValuerollingMaxDate
p04NameParameter: Column

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

D s also
labelexample_rolling_date_functions