Custom Date Parser Script - Convert Multiple Date Formats at Once
I recently was handed an extract that contained several date columns in which dates were recorded in multiple formats. Converting strings to dates works automatically in QlikView as long as the string is in the default date format expected. This is set in your environment variables at the start of the load script and is dependant on the localisation settings on your PC. For example, for the Australian region the default expected is d/mm/yyyy.
You can of course override the default date format expected when loading strings as dates, by using the date() function and supplying a specific format string to use when interpreting the strings as dates. But this only allows you to supply a single format string, so it requires that all dates in the column are in the same format. For my data set this was not the case.
One of the cool things about QlikView is how extensible the platform is. You can write your own functions in the Edit Module section and use these functions within the QlikView as though they are inbuilt functions. So I set about creating my own function that would interpret multiple date formats in the one pass. I thought I would share the resulting code on community so others could leverage it, and even expand or improve on it. The screen shot below shows the formats it currently handles. Note that it should work with US date formats where the month comes before the day, but I have not tested on such data so i would be interested to hear some feedback if you try it.