topic Re: Date of Birth Incorrect in QlikView

Date of Birth Incorrect

gerhardl — Fri, 09 Mar 2012 13:52:00 GMT

Hi,

We have an extract with a [Date of Birth] field. The extract has a funny date format - DD-MMM-YY (e.g. 09-MAR-85).

In my script I change all my date formats like this:

DATE(DATE#( [Date of birth], 'DD-MMM-YY')) as [Date of birth],

What I noticed is that for certain records QlikView then assumes the wrong century. So for certain accounts the date of birth will then be correct - 1985/03/09.

For other accounts the date of birth will be 2085/03/09.

Any idea how I can fix this?

Thanks in advance,

Gerhard.

Re: Date of Birth Incorrect

swuehl — Fri, 09 Mar 2012 14:34:33 GMT

Parsing the dates in using date# with only two digit year number is just not fully determined. No idea how QlikView determines the right century, I think that's wild guessing. In my script, it changes the century 1962, so for years <62, it assumes 21 century, then 20th century. Maybe 50 years back in time from now? no idea.

To fix it, I think you need to add the year to your [Date of birth] using string operations, then parse the full four digit year in.

Date(date#(left(DoB,7)&'20'&right(DoB,2),'DD-MMM-YYYY') as DoB,

to use only 2000 to 2099 DoB.

No idea how you should determine the correct century, that's up to you...

Date of Birth Incorrect

CELAMBARASAN — Fri, 09 Mar 2012 14:40:57 GMT

Hi,

Can you tell me whats your date of birth year range?

Celambarasan

Re: Date of Birth Incorrect

Tue, 30 Oct 2012 08:17:45 GMT

Hello,

my Workarround:

if( Date(Date#(@211:216,'DDMMYY')) < AddYears(today(), -15),

Date(Date#(@211:216,'DDMMYY')),

AddYears(Date(Date#(@211:216,'DDMMYY')), -100)

) as [Day of birth]