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.
Group Admin Team