topic Re: Create a date from a 6 digit code in QlikView

Create a date from a 6 digit code

Mon, 14 Nov 2016 06:45:44 GMT

Good day,

I want to convert the REGISTRATION_MONTH_CODE into a date with the following format YYYY/MM/DD.

the first four numbers are a Year, next two are a month and then I will add 01 as the day.

Your assistance will be highly appreciated.

Kind regards,

Mzoxolo Mbini

Chanty4u — Mon, 14 Nov 2016 06:51:08 GMT

hi,

Load*,

Date(Date#(Datefield,'YYYYMMDD'),'YYYY/MM/DD') AS newdate

resident oldtable;

Chanty4u — Mon, 14 Nov 2016 07:01:15 GMT

check sample example:

aa:

LOAD * INLINE [

Dat

20140812

20140611

20140410

];

Result:

LOAD *

date(Date#(Dat,'YYYYMMDD'),' YYYY/MM/DD') as newdate

Resident aa;

its_anandrjs — Mon, 14 Nov 2016 07:07:11 GMT

Try with this

Load

REGISTRATION_MONTH_CODE

Date(REGISTRATION_MONTH_CODE,'YYYY/MM/DD') as New_REGISTRATION_MONTH_CODE

From Location;

Note:- Be insure you have proper date format for the field REGISTRATION_MONTH_CODE

if not then try with this

Date(Date#(REGISTRATION_MONTH_CODE,'YYYYMMDD'),'YYYY/MM/DD') as New_REGISTRATION_MONTH_CODE

Regards,

Anand