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 next

This example shows how you can use the following functions to perform some analysis on Datetime columns.

  • MINDATE - Calculates the earliest (minimum) date from a column of Datetime column values. See MINDATE Function.
  • MAXDATE - Calculates the latest (maximum) date from a column of Datetime column values. See MAXDATE Function.
  • MODEDATE - Calculates the most frequent (mode) date from a column of Datetime column values. See MODEDATE Function.

Source:

The following dataset contains a set of three available dates for a set of classes:

classIdDate1Date2Date3
c0012020-03-092020-03-132020-03-17
c0022020-03-092020-03-062020-03-21
c0032020-03-092020-03-162020-03-23
c0042020-03-092020-03-232020-04-06
c0052020-03-092020-04-092020-05-09
c0062020-03-092020-08-092021-01-09

Transformation: 

To compare dates across multiple columns, you must consolidate the values into a single column. You can use the following transformation to do so:

D trans
RawWrangletrue
Typestep
WrangleTextunpivot col: Date1,Date2,Date3 groupEvery: 1
p01NameColumns
p01ValueDate1,Date2,Date3
p02NameGroup size
p02Value1
SearchTermUnpivot columns

The dataset is now contained in three columns, with descriptions listed below:

classIdkeyvalue
Same as previous.DateX column identifierCorresponding value from the DateX column.

You can use the following to rename the value column to eventDates:

D trans
RawWrangletrue
p03ValueeventDates
Typestep
WrangleTextrename type: manual mapping: [value,'eventDates']
p01NameOption
p01ValueManual rename
p02NameColumn
p02Valuevalue
p03NameNew column name
SearchTermRename columns

Using the following transformations, you can create new columns containing the min, max, and mode values for the Datetime values in eventDates:

D trans
RawWrangletrue
p03ValueearliestDate
Typestep
WrangleTextderive type: single value: MINDATE(eventDates) as: 'earliestDate'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueMINDATE(eventDates)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03ValuelatestDate
Typestep
WrangleTextderive type: single value: MAXDATE(eventDates) as: 'latestDate'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueMAXDATE(eventDates)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03ValuemostFrequentDate
Typestep
WrangleTextderive type: single value: MODEDATE(eventDates) as: 'mostFrequentDate'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueMODEDATE(eventDates)
p03NameNew column name
SearchTermNew formula

Results:

classIdkeyeventDatesmostFrequentDatelatestDateearliestDate
c001Date12020-03-092020-03-092021-01-092020-03-06
c001Date22020-03-132020-03-092021-01-092020-03-06
c001Date32020-03-172020-03-092021-01-092020-03-06
c002Date12020-03-092020-03-092021-01-092020-03-06
c002Date22020-03-062020-03-092021-01-092020-03-06
c002Date32020-03-212020-03-092021-01-092020-03-06
c003Date12020-03-092020-03-092021-01-092020-03-06
c003Date22020-03-162020-03-092021-01-092020-03-06
c003Date32020-03-232020-03-092021-01-092020-03-06
c004Date12020-03-092020-03-092021-01-092020-03-06
c004Date22020-03-232020-03-092021-01-092020-03-06
c004Date32020-04-062020-03-092021-01-092020-03-06
c005Date12020-03-092020-03-092021-01-092020-03-06
c005Date22020-04-092020-03-092021-01-092020-03-06
c005Date32020-05-092020-03-092021-01-092020-03-06
c006Date12020-03-092020-03-092021-01-092020-03-06
c006Date22020-08-092020-03-092021-01-092020-03-06
c006Date32021-01-092020-03-092021-01-092020-03-06