topic Re: Date formatting problem in QlikView

Date formatting problem

Fri, 21 Feb 2014 08:47:45 GMT

I got a field (Bdate) which consists of a birthdate in a textformat like this 050453.

I´d like to load the field formatting it to a real date and looking like this 05-04-1953 so I can make a script that calculates the age

AGE(today(),Bdate) as Personage

What do I do?

Re: Date formatting problem

alexandros17 — Fri, 21 Feb 2014 08:58:01 GMT

You need at first to distinguish years before 2000:

so you may use this logic:

MakeDate(If(Right(Bdate,2) > Right(Year(Today()),2), 1900, 2000)+Right(Bdate, 2),Mid(Bdate,3,2),Left(Bdate,2) )

Re: Date formatting problem

manojkulkarni — Fri, 21 Feb 2014 08:58:18 GMT

Birth date string is constant, then you can use string functions to create date field. But the problem is with year part of the string. If your data is starting from specific year then you can head with assumptions.

Re: Date formatting problem

CELAMBARASAN — Fri, 21 Feb 2014 09:00:30 GMT

Use the below expression to create the field and make use of it for age calculation

AddYear(Date(Date#(Bdate, 'DDMMYY')), IF(Right(Bdate, 2) > Right(Year(Today()), 2), -100, 0)) AS BirthDateField

Re: Date formatting problem

amit_saini — Fri, 21 Feb 2014 09:10:02 GMT

Hi Please use this:

MakeDate(Year(Today()),Month(DATE(DATE#( left(DOB,11), 'DD-MMM-YY')) ),Day(DATE(DATE#( left(DOB,11), 'DD-MMM-YY')) )) as Birth_date

Thanks,

Re: Date formatting problem

MK_QSL — Fri, 21 Feb 2014 09:38:31 GMT

=Age(Today(),Date(Date#(Bdate,'DDMMYY')))

Re: Date formatting problem

Anonymous — Fri, 21 Feb 2014 10:11:33 GMT

Hi Soren,

Try this Date(Bdate,'DD-MM-YYYY') It will give the full date formate

after that u can find the age as - AGE(Today(),Date(Bdate,'DD-MM-YYYY'))

Hope this would be help u ...

Re: Date formatting problem

Anonymous — Fri, 21 Feb 2014 19:05:55 GMT

There is no absolutely reliable way to get the year right (remember Y2K?). 022114 could be February 21, 2014 or February 21, 1914. That means you have to use some rules, for example if YY<"current year", use 20YY, otherwise use 19YY. You'll certainly have problems if the birth date is in 1914 or earlier.

BTW, Celambarasan Adhimulam is using this rule, but the rule could be different.

Re: Date formatting problem

hic — Fri, 21 Feb 2014 19:20:25 GMT

1. QlikView interprets (YY=) "64" as 1964 but "63" as 2063. Try the following code and you'll see:

Load *, Date(Date#(OriginalDate,'YYMMDD'),'YYYY-MM-DD') as Date;

Load Num(RecNo()-1,'00') & '0331' as OriginalDate Autogenerate 100;

So if you know that "53" means 1953, you will need to code this in your formula, with e.g. an If()-function.

2. There is usually no purpose in using the Date()-function if you wrap it in another function. For example, the Date()-function serves no purpose in

Age(Today(),Date(Date#(Bdate,'DDMMYY')))

Better to write

Age(Today(),Date#(Bdate,'DDMMYY'))

HIC

Re: Date formatting problem

Anonymous — Fri, 21 Feb 2014 21:54:56 GMT

Try this

If(Right(BDate,2) >= 30,Date(MakeDate('19'&Right(BDate,2),Left(BDate,2),Mid(BDate,3,2)),'MM-DD-YYYY'),Date(MakeDate('20'&Right(BDate,2),Left(BDate,2),Mid(BDate,3,2)),'MM-DD-YYYY')) as BirthDate

Re: Date formatting problem

Sat, 22 Feb 2014 05:34:17 GMT

can use Or Age

Age (Date(today(),Date(Date#(Bdate,'DDMMYY'),'DD-MM-YYYY')))

other function is interval

Re: Date formatting problem

Tue, 25 Feb 2014 11:28:37 GMT

Using this IF makes no ages - its all blank

Re: Date formatting problem

Tue, 25 Feb 2014 11:30:00 GMT

I now got a date like this 07-06-64

How would you make a script solving the problem with 64 and 63

Re: Date formatting problem

hic — Tue, 25 Feb 2014 11:38:24 GMT

You need to decide yourself which cutoff you want. One way is to use

If( Year(Date) > 2020, AddYears(Date,-100), Date) as NewDate

where Date is your expression that interprets the date. Then all dates after 2020 will be "moved" 100 years back in time.

HIC

Re: Date formatting problem

Tue, 25 Feb 2014 11:46:31 GMT

I´m not quite sure that I understand.

I got a field AGE starting with -50 to 50.

All ages with minus got birthdates before 1964.

All positiv numbers are ok ages

Re: Date formatting problem

hic — Tue, 25 Feb 2014 12:09:53 GMT

Then you can use an expression where you don't accept negative ages:

IF( Age<0, Age+100, Age) as NewAge

HIC

Re: Date formatting problem

DavidFoster1 — Tue, 25 Feb 2014 12:23:32 GMT

Sorry to moan but...

Can people please read whole threads before responding. There are so many similar responses here that it is difficult to find the best one. In this case please look at Henric's reponse who (as usual) has it spot-on.