MODEDATE Function
Computes the most frequent (mode) value found in all row values in a Datetime column.
If a row contains a missing or null value, it is not factored into the calculation. If no Datetime values are found in the source column, the function returns a null value.
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
modedate(myDates)
Output: Returns the most frequently appearing Datetime value from the myDates column.
Syntax and Arguments
modedate(function_col_ref)
Argument | Required? | Data Type | Description |
|---|---|---|---|
function_col_ref | Y | string | Name of column to which to apply the function |
For more information on syntax standards, see Language Documentation Syntax Notes.
function_col_ref
Name of the column the Datetime values of which you want to calculate the most frequent (mode) date.
Column must contain Datetime values.
Literal values are not supported as inputs.
Multiple columns and wildcards are not supported.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | Datetime (column reference) | datTransactions |
Examples
Astuce
For additional examples, see Common Tasks.
This example shows how you can apply statistical functions on Datetime columns.
Functions:
Item | Description |
|---|---|
MINDATE Function | Computes the minimum value found in all row values in a Datetime column. |
MAXDATE Function | Computes the maximum value found in all row values in a Datetime column. |
MODEDATE Function | Computes the most frequent (mode) value found in all row values in a Datetime column. |
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:
Transformation Name | |
|---|---|
Parameter: Columns | Date1,Date2,Date3 |
Parameter: Group size | 1 |
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:
Transformation Name | |
|---|---|
Parameter: Option | Manual rename |
Parameter: Column | value |
Parameter: New column name | eventDates |
Using the following transformations, you can create new columns containing the min, max, and mode values for the Datetime values in eventDates:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | MINDATE(eventDates) |
Parameter: New column name | earliestDate |
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | MAXDATE(eventDates) |
Parameter: New column name | latestDate |
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | MODEDATE(eventDates) |
Parameter: New column name | mostFrequentDate |
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 |