topic Birthday field in QlikView

Birthday field

gerhardl — Thu, 01 Mar 2012 06:42:20 GMT

Hi there,

I am currently loading a bunch of our system-generated reports (.txt files) into QV to do my own analysis.

We are going to start sending Birthday SMS's to all our customers - until we developed this functionality in the system I will be manually extracting the Birthday Lists every morning.

In our Account Extract we have a [Date of Birth] field in this format: DD-MMM-YY (e.g. 01-MAR-85). In some extracts the dates also have timestamps, so:

I import this in the following way:

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

This then gives me all dates as YYYY/MM/DD.

What I want to do is load a new field (derived from Date of Birth) called Birthday - the field should be equal to the Date and Month and the Current Year, e.g. if DOB is 1985/03/01 then Birthday must be 2012/03/01.

I can't figure this out - any ideas?

Thanks,

Gerhard

Birthday field

CELAMBARASAN — Thu, 01 Mar 2012 06:51:57 GMT

Hi,

Have you tried with this?

MakeDate(Year(Today()),Month([Date of birth]),Day([Date of birth])) as Birthday

MakeDate(Year(Today()),Month(DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) ),Day(DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) )) as Birthday

Hope it helps

Celambarasan

Birthday field

gerhardl — Thu, 01 Mar 2012 07:21:42 GMT

Thanks!

Second option worked perfectly.

Birthday field

CELAMBARASAN — Thu, 01 Mar 2012 07:25:09 GMT

Hi,

I think [Date of birth] field doesn't have a proper date format is it so?

Celambarasan

Birthday field

gerhardl — Thu, 01 Mar 2012 07:38:12 GMT

Hi,

Do you mean in the original extract I pull in? No - it is DD-MMM-YY.

But once I pull it into QV using DATE(DATE#( left([Date of birth],11), 'DD-MMM-YY')) as [Date of birth],

it does.