Working with dates
Dates can come in a range of formats: ‘month-day-year’, ‘year/month/day’, etc.
A common scenario is to ingest dates from a variety of sources, cleanse and standardise them,
perform calculations using the dates, then output the results to a different format for compatibility with a downstream system
(risk model, data warehouse, etc).
Quantemplate uses the Basic ISO date format
yyyymmdd
for all date calculations and transformations.
Columns formatted as dates in Excel files (.xlsx, .xlsm, .xls) are automatically converted to Basic ISO format on uploading to Quantemplate
(these can then be checked in the Date Cleanse operation).
All other date formats, including dates from CSV file imports, need to be converted using the Date Cleanse operation.
There are three groups of date functionality in Quantemplate:
- operation converts dates to Quantemplate’s standard format (Basic ISO:
yyyymmdd
),
removes invalid dates and replaces them with blank values so that the columns can be used in date functions in Calculate. Invalid dates can be automatically captured in a Validation Report by enabling the validation option in the operation.
This operation should be used before any date calculations are performed, typically in the earlier stages.
- in Calculate perform calculations
and transformations on cleansed dates
(
DATECALC
,
DURATION
,
ENDOFMONTH
,
etc).
- operation formats dates for downstream systems, such as a data warehouse or risk model.
Once dates have been reformatted in the Date Output operation they become text values and can no longer be used in date functions. This operation should be used after all required date calculations have been performed.
Dates are values using components (days, months, quarters, years) and separators
./-
to refer to a specific day.
Components supported by date operations
|
|
|
|
9 |
One-digit day |
|
09 |
Two-digit day |
|
Mon |
Short day name (Date Output only) |
|
Monday |
Long day name (Date Output only) |
|
4 |
One-digit month |
|
04 |
Two-digit month |
|
Apr |
Short month name |
|
April |
Long month name (Date Output only) |
|
80 |
Short year |
|
1980 |
Long year |
|
Q1 |
Quarter with prefix Q |
|
. |
Separator . |
|
- |
Separator - |
|
/ |
Separator / |
|
␣ |
Separator ‘space’ |
|
.-/␣ |
Any separator (Date Cleanse only) |
Examples
|
|
|
29/06/2012 |
|
06-29-2012 |
|
20120629 |
|
Q1-2016 |
|
June 2016 |
Number of components
Date Cleanse can interpret dates that have:
- Three components: a year, a month and a day.
- Two components: a year combined with a month or quarter. These are known as partial dates and need to be converted to a specific day.
Values which comprise only a four-digit year are not supported as dates, but the
general functions in Calculate and Validation should be sufficient for dealing with them.
Date Output supports any number or combination of components.
Quarters
A common date component is the Quarter, usually indicated by the quarter prefix
Q
followed by digits 1, 2, 3 or 4, e.g.
2016Q3
,
Q1-2017
.
Quantemplate supports the quarter prefix
Q
.
The quarter-detection in Date Cleanse is not case-sensitive:
q1
and
Q1
will both be recognised as quarters.
If your data requires support for a different quarter prefix, contact
support@quantemplate.com.
Valid date ranges
The
Date Cleanse operation will count dates between the years 1000 and 2999 as valid.
If you need to check that dates fall within a more specific range, create a rule in the
Validation operation.
Legacy date operations
The following script operations will soon be deprecated:
Once deprecated, it will no longer be possible to add these operations to a pipeline. They will continue to work in existing pipelines. Updating existing pipelines to remove legacy date operations is advised.
