Data Quality Management Checks - Handling Different Date Formats

1 post / 0 new
Brian Element's picture
Brian Element
Online
Joined: 07/11/2012 - 19:57
Data Quality Management Checks - Handling Different Date Formats

Hello Group Members,

While System generated Reports largely have a uniform Date format for Date fields in the Report, Non-System Reports may have Dates which are manually entered by the Business User creating a situation with varying Date formats. Such fields need to be cleaned up and generalised to facilitate Date based analytics in IDEA.
 
Let us examine this case with an example ~
 
You have a data dump in IDEA with a sample field 'Incident Date' which has the sample Dates - 05.01.19, 05-JAN-2019 and 05/01/2019
 
Now 'Incident Date' appears in IDEA as a Character field.
 
The need of the moment is to convert this field having varying Date formats to a consistent and uniform Date format in IDEA so as to analyze 'Incidents' reported on 1st January 2019
 
This can be done by appending a Virtual Date field titled 'Incident Date IDEA' with the criteria ~
 
@compif(@len(Incident Date) = 8, @ctod(Incident Date, "DD.MM.YY"), @len(Incident Date) = 11, @ctod(Incident Date, "DD-MMM-YYYY"), @len(Incident Date) = 10, @ctod(Incident Date, "DD/MM/YYYY"))
 
The criteria above in essence maps the length of the Incident Date in a Character format to the appropriate Date mask for conversion from Character to Date.
 
So 05.01.19 has a length of 8 and the mask for conversion would be DD.MM.YY
 
In the criteria above @Len() is used to capture the length of the Incident Date and @ctod() is used to convert Incident Date in a Character format to Incident Date in a Date format.
 
Best Regards
 
Group Admin Team