topic Re: Re: Re: Date Conversion in QlikView

Date Conversion

sona_sa — Thu, 03 Jul 2014 12:12:17 GMT

Hi Can anyone change the date from in DD-MMM-YYYY in QlikView. Please find the attached xls for the test purpose.

Thanks

Re: Date Conversion

Gysbert_Wassenaar — Thu, 03 Jul 2014 12:15:54 GMT

LOAD date(timestamp#(entry_date,'MMM D YYYY hh:mmTT'),'DD-MM-YYYY') as Date

FROM [DATE.xlsx] (ooxml, embedded labels, table is Sheet1);

See attached qvw.

Re: Re: Date Conversion

sona_sa — Thu, 03 Jul 2014 12:42:28 GMT

Thanks Gysbert, for reply and solution.

But when I am fetching the data from my xls this is providing from 10th of every month. for 1st to 9th date are missing. Please find the attached xls for full month data with 3 months.

Re: Re: Re: Date Conversion

MK_QSL — Thu, 03 Jul 2014 12:53:00 GMT

Try below two convert your dates...

DATE(ALT(Date#(entry_date,'MMM D YYYY hh:mmTT'),Date#(entry_date,'MMM DD YYYY hh:mmTT')),'DD-MMM-YYYY') as entry_date

Re: Date Conversion

Gysbert_Wassenaar — Thu, 03 Jul 2014 12:55:55 GMT

The double spaces throw the timestamp# function off. Try this:

date(timestamp#(replace(entry_date,' ',' '),'MMM D YYYY hh:mmTT'),'DD-MM-YYYY') as Date

Re: Date Conversion

vardhancse — Thu, 03 Jul 2014 12:58:43 GMT

Change the date format here.