topic Loading problem with Date which is in YYMMDD in QlikView

Loading problem with Date which is in YYMMDD

Sat, 06 Apr 2013 13:18:05 GMT

I am trying to load data which is a numeric field in YYMMDD format and convert it to YYYYMMDD format

I have tried the following in the script:

Date(

makedate('20'&left([Creation Date],2),mid([Creation Date],3,2), right([Creation Date],2)),'YYYY/MM/DD') as CDate

Creation Date is 6 digits (YYMMDD) but for year less then 10 there is no leading zero.

For Eg 51205 which represents date 5th Dec 2005 CDate is converted to -

For Eg 60103 which represents date 3rd Jan 2006 CDate is converted to 2060/10/03

Can someone please help me solve the problem so I can get correct dates.

Thanks

Re: Loading problem with Date which is in YYMMDD

er_mohit — Sat, 06 Apr 2013 13:34:44 GMT

Try This

date( date#('20' &[Creation Date],'YYYYMMD'),'YYYY/MM/DD')

as example this copy and paste this code

LOAD date( date#('20' &[Creation date],'YYYYMMD'),'YYYY/MM/DD') as create,* INLINE [

Creation date

60103

05024

];

Re: Loading problem with Date which is in YYMMDD

Sun, 07 Apr 2013 04:21:49 GMT

Hi ,

Please try the below code .

=date( date#( num( '60501','000000') ,'YYMMDD'),'YYYYMMDD')

Please let me know for the same .

Thanks

Re: Loading problem with Date which is in YYMMDD

kaushiknsolanki — Sun, 07 Apr 2013 06:13:34 GMT

Try this.

Load

Date(Date#(if(Len([Creation Date]) = 5,'0'&[Creation Date],[Creation Date]),'YYMMDD'),'DD/MM/YYYY') as New_Date inline [
Creation Date
51205
60103
091010
]

;

Regards,

Kaushik Solanki

Re: Loading problem with Date which is in YYMMDD

Mon, 08 Apr 2013 07:40:22 GMT

That solved my problem.

Thanks

Re: Loading problem with Date which is in YYMMDD

Mon, 08 Apr 2013 17:51:37 GMT

Hi ,

Please try the below code .

=date( date#( num( '60501','000000') ,'YYMMDD'),'YYYYMMDD')

Please let me know for the same .

Thanks