topic Re: Date format giving incorrect year in QlikView

Date format giving incorrect year

Mon, 07 Mar 2016 10:04:39 GMT

Hi there!

I have date of births in the following format (I.e. YYMMDD):

DOB

391107

600116

801008

...etc.

I've used the following in my script editor to create a date formatted field:

Date(Date#(DOB, 'YYMMDD'), 'DD/MM/YYYY') as DOB_FORMATTED

My output is showing as follows:

DOB_FORMATTED

07/11/2039

16/01/2060

08/10/2080

...etc.

I would like it to show as per below:

07/11/1939

16/01/1960

08/10/1980

...etc.

How can I achieve this?

Re: Date format giving incorrect year

sunny_talwar — Mon, 07 Mar 2016 10:43:00 GMT

Try this may be:

Date(Date#(19 & DOB, 'YYYYMMDD'), 'DD/MM/YYYY') as DOB_FORMATTED

Re: Date format giving incorrect year

marcus_sommer — Mon, 07 Mar 2016 11:55:01 GMT

In addition to the suggestion from Sunny you might need a further condition to differenciate between 1900 and 2000 like:

= if(mid(391107, 1, 2) <= mid(year(today()), 3, 2), 20, 19)

in the assumption that 19 might not always correct (but you can't avoid with this format all inaccuracies).

- Marcus

Re: Date format giving incorrect year

swuehl — Mon, 07 Mar 2016 12:17:01 GMT

I agree with Marcus.

Your data is just not well defined, i.e. you can't tell from the data which century you should take.

If you are looking at DOB for turtles, maybe also 19th century (like 1855) might be a valid one.

QlikView will just assume that the Year is in a range +-50 years from today (so I don't think it will return 2080 with your sample data).

You can make some assumptions (humans, no DOB in the future, not older than 100 years), then go with something like Marcus suggested.

But there probably will be cases where your assumptions fail. So look out for some additional information that helps you retrieve the correct century.

Good luck,

Stefan