topic Re: Date in QlikView

Date

smilingjohn — Thu, 25 Feb 2016 03:43:21 GMT

I have the date format like this

8/9/2015 12:00:00AM

I want to have only Date like 8/9/2015

How to achieve this at the sript level.

PLease find the attachment below ,with sample Excel file

Thanks in advance

Re: Date

sunny_talwar — Thu, 25 Feb 2016 03:44:53 GMT

Try this:

Date(Floor(DateField)) as Date

or this:

Date(Date#(Left(DateField, 8), 'M/D/YYYY'), 'M/D/YYYY') as Date (assuming your date is M/D/YYYY, but if it is not can change it to D/M/YYYY)

Re: Date

smilingjohn — Thu, 25 Feb 2016 03:47:28 GMT

No its not working

Re: Date

sunny_talwar — Thu, 25 Feb 2016 03:48:30 GMT

Updated my comment to add another option. Can you try the other method

Re: Date

smilingjohn — Thu, 25 Feb 2016 03:56:20 GMT

HISunny,

Please find the attachment of XL File and QVW ,

I followed your procedure the date is converting but the master caledar is not working .

Re: Date

Thu, 25 Feb 2016 03:58:53 GMT

Use the following at the start of your qvd script:

SET DateFormat='D/M/YYYY';

Re: Date

sunny_talwar — Thu, 25 Feb 2016 04:05:07 GMT

Try this:

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY hh:mm:ssTT';

Orders:

LOAD OrderDate as OrderDate,

Date(Floor(OrderDate)) as Date

FROM

[Date.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date

smilingjohn — Thu, 25 Feb 2016 04:12:45 GMT

Hi Sunny ,

Now the master calendar is not working

PFA

Re: Date

sunny_talwar — Thu, 25 Feb 2016 04:17:03 GMT

Try the attached may be

Re: Date

senpradip007 — Thu, 25 Feb 2016 04:27:30 GMT

Try like

Orders:

LOAD OrderDate,

date(Timestamp#(OrderDate, 'MM/DD/YYYY hh:mm:ssTT')) as Date

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Date

Anonymous — Thu, 25 Feb 2016 04:46:01 GMT

Date(Date#((DateField, 8), 'M/D/YYYY'), 'M/D/YYYY') as Date in this expression Datefield later to give the your format than it is working

Re: Date

HirisH_V7 — Thu, 25 Feb 2016 05:09:58 GMT

Hi,

Like this ways,

Date(Floor(OrderDate)) as OrderDate,
  OrderDate,
  Subfield(OrderDate,' ',1) as Date,
  Date(Timestamp(OrderDate,'M/D/YYYY  hh:mm:ssTT'),'M/D/YYYY') as Time,
  Trim(Mid(OrderDate,1,10)) as Datee

Here your calendar is working fine,

PFA,

HTH,

Hirish