Issue with Rogue Date

podge2019 — Wed, 07 Apr 2021 16:06:16 GMT

Hi,

Working with some dirty date data and looking for some assistance.

Have a spreadsheet with multiple versions of Policy Start Date.

To try to clean this up in the script I have this long winded entry in my script.

[Policy Start Date] AS CP_Policy_Start_Date,
Date(Floor(Alt(Num([Policy Start Date]), Num(Date#([Policy Start Date], 'DD-MM-Y'
)), Num(Date#([Policy Start Date], 'DD-MM-YY'
)), Num(Date#([Policy Start Date], 'DD-M-YYYY'
)), Num(Date#([Policy Start Date], 'DD-MM-YY'
)), Num(Date#([Policy Start Date], 'DD-MMM-YY'
)), Num(Date#([Policy Start Date], 'DD-MMM-YYYY'
)), Num(Date#([Policy Start Date], 'DD/M/YYYY'
)), Num(Date#([Policy Start Date], 'DD/MM/YYYY'
)), Num(Date#([Policy Start Date], 'DD/M/YY'
)), Num(Date#([Policy Start Date], 'DD/MM/YY'
)), Num(Date#([Policy Start Date], 'DD.M.YYYY'
)), Num(Date#([Policy Start Date], 'DD.MM.YYYY'
)), Num(Date#([Policy Start Date], 'DD.MMM.YYYY'
)), Num(Date#([Policy Start Date], 'DD.M.YYYY'
)), NUM(Date#([Policy Start Date], 'DDMMYYYY')))), 'DD/MM/YYYY')
AS Policy_Start_Date,

It does does the job except for one rogue date 19122005 which then comes into QlikView as 08/04/54254

Any guidance on how I can change my script to capture this date? Its one rogue date out of thousands!! 😀

Thanks

topic Issue with Rogue Date in QlikView

Issue with Rogue Date