3 Replies Latest reply: Oct 30, 2012 4:17 AM by Markus Bauernschmitt RSS

    Date of Birth Incorrect

    Gerhard Laubscher

      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
          Stefan Wühl

          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.

           

          Like

          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 Adhimulam

            Hi,

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

             

            Celambarasan