Contents:
Computes the maximum 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
maxdate(myDates)
Output: Returns the maximum Datetime value from the myDates column.
Syntax and Arguments
maxdate(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 maximum 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 |
Tip: For additional examples, see Common Tasks.
Examples
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 | Unpivot columns |
|---|---|
| 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 | Rename columns |
|---|---|
| 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 | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | MINDATE(eventDates) |
| Parameter: New column name | earliestDate |
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | MAXDATE(eventDates) |
| Parameter: New column name | latestDate |
| Transformation Name | New formula |
|---|---|
| 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 |
This page has no comments.