How to Handle Inconsisent Date Format in Qlik DataSources

In some situations, double digit months were causing certain dates to be excluded from QlikView charts. The data was showing up as Null for these dates.

Environment:
QlikView 12.20
Qlik Sense November 2018

The cause was of this issue was two part. The inconsistent format of the source data and the function used in the script. As the data changes the script to load the data may sometimes need to be adjusted as well. The subfield function was much better suited for this task for this example,

Note: Although this article is written for QlikView, the same functions and techniques will work for Qlik Sense as well. The same functions are available for both products.

The date data had the following format:

1/02/19 4:00 PM GMT -05:00 US/Canada Eastern
12/25/18 1:00 PM GMT -05:00 US/Canada Eastern

The data load script was initially using the left function to trim the timestamp data to get the date information. This is not a good technique when the data being loaded is not always uniform number of characters for days, months and years. For this specific example, when the data had months with double digits, this caused the script to no longer work properly and those dates with double digit months were displaying as null in the chart.

A better technique for this situation is to use the subfield function which can be used to get part of a string based on delimter.
Here is a link to the Nov 2018 QlikView Subfield documentation on help.qlik.com.

The expression used in the load script to get the date to show correctly in the chart was:
Date(Date#(SubField([End Date],' ',1), 'MM/DD/YY'), 'MM/DD/YYYY') as [YourDateField],

The subfield was used to get the left most part of the string, up the first space. Then the date and dat# functions were used to ensure that the data was date and displayed with the desired MM/DD/YYY format. This worked for this scenario and properly handled the single digit and double digit months.

The date and date# functions are used to interpret the data and format the display of the date information. This is covered in the following community post: date and date# functions. There are additional examples on the help.qlik.com site for date#() and date(). Links to the Nov 2018 QlikView version are given here as an example.