This example shows how you can apply statistical functions on Datetime columns. |
Functions:
Source:
The following dataset contains a set of three available dates for a set of classes:
| classId | Date1 | Date2 | Date3 |
|---|---|---|---|
| c001 | 2020-03-09 | 2020-03-13 | 2020-03-17 |
| c002 | 2020-03-09 | 2020-03-06 | 2020-03-21 |
| c003 | 2020-03-09 | 2020-03-16 | 2020-03-23 |
| c004 | 2020-03-09 | 2020-03-23 | 2020-04-06 |
| c005 | 2020-03-09 | 2020-04-09 | 2020-05-09 |
| c006 | 2020-03-09 | 2020-08-09 | 2021-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:
The dataset is now contained in three columns, with descriptions listed below:
| classId | key | value |
|---|---|---|
| Same as previous. | DateX column identifier | Corresponding value from the DateX column. |
You can use the following to rename the value column to eventDates:
Using the following transformations, you can create new columns containing the min, max, and mode values for the Datetime values in eventDates:
Results:
| classId | key | eventDates | mostFrequentDate | latestDate | earliestDate |
|---|---|---|---|---|---|
| c001 | Date1 | 2020-03-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c001 | Date2 | 2020-03-13 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c001 | Date3 | 2020-03-17 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c002 | Date1 | 2020-03-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c002 | Date2 | 2020-03-06 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c002 | Date3 | 2020-03-21 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c003 | Date1 | 2020-03-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c003 | Date2 | 2020-03-16 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c003 | Date3 | 2020-03-23 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c004 | Date1 | 2020-03-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c004 | Date2 | 2020-03-23 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c004 | Date3 | 2020-04-06 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c005 | Date1 | 2020-03-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c005 | Date2 | 2020-04-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c005 | Date3 | 2020-05-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c006 | Date1 | 2020-03-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c006 | Date2 | 2020-08-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |
| c006 | Date3 | 2021-01-09 | 2020-03-09 | 2021-01-09 | 2020-03-06 |