topic Re: Removing Time Stamp From Date in QlikView

Removing Time Stamp From Date

Wed, 22 Oct 2014 14:21:17 GMT

I am currently trying to create a calendar based on data I am loading from a database table using SQL Select. The initial date field contains a time stamp which I want to strip out. The reason for this is that I have found that when I export the chart information created in QlikView from the data into Excel it is displaying the date incorrectly. A 'Week End' date displayed as 07/09/2014 in QlikView is shown as 08/09/2014 in Excel. The contents of the cell are 07/09/2014 00:00:00 but it is displayed as 08/09/2014.

I have tried removing the time stamp from the data loaded into QlikView with a preceding load statement but it is still exporting the date to Excel with a time stamp.

Table:

LOAD [Customer]

,[Order No]

,Date ([Dispatch Date],'DD/MM/YYYY') As [Dispatch Date]

,etc;

SQL Select *

From Datawarehouse.dbo.Orders

Where .......;

TempDates:

LOAD NUM(Min([Dispatch Date],)) as MinDate,

NUM(MAX([Dispatch Date],)) as MaxDate

RESIDENT Table;

LET vMinDate = peek('MinDate',0,'TempDates');

LET vMaxDate = peek('MaxDate',0,'TempDates');

Drop Table TempDates;

TempCalendar:

LOAD

$(vMinDate) + ROWNO() - 1 AS Num,

DATE($(vMinDate) + ROWNO() - 1) AS TempDate

AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;

OrderCalendar:

LOAD

Usual DATE definitions,

WeekEnd(TempDate) As WeekEnd,

etc;

Initially I tried a straight forward CONVERT statement (date format 103) within the SQL Select (having defined the fields to bring in and with no preceding load) but when exporting the data from the QlikView chart it still had a time stamp.

How can I bring in only the date part of the source data or manipulate it with in QlikView so that the TempDate & WeekEnd are exported without a time stamp.

Your help is much appreciated.

Re: Removing Time Stamp From Date

rubenmarin — Wed, 22 Oct 2014 14:33:40 GMT

Hi Gareth, Weekend returns 07/09/2014 23:59:59.999, maybe Excel is rounding

In OrderCalendar try:

Date(Floor(WeekEnd(TempDate)),'DD/MM/YYYY')) As WeekEnd,

If I create a table with the expression

WeekEnd(Date('07/09/2014 00:00:00', 'DD/MM/YYYY')) It exports as 08/09/2014

If I create a table with the expression

Date(Floor(WeekEnd(Date('07/09/2014 00:00:00', 'DD/MM/YYYY'))), 'DD/MM/YYYY') it exports as 07/09/2014

Re: Removing Time Stamp From Date

stigchel — Wed, 22 Oct 2014 14:34:31 GMT

In the preceding load you can use:

Date(Floor([Dispatch Date]),'DD/MM/YYYY') as [Dispatch Date]

Re: Removing Time Stamp From Date

maxgro — Wed, 22 Oct 2014 18:12:58 GMT

if you have a datetime (now() in the example), use floor and frac to get the date or the time

Re: Removing Time Stamp From Date

Gabriel — Wed, 22 Oct 2014 18:23:13 GMT

Hi Gareth,

Try

=Date(Floor(DateField())) AS [Dispatch Date]

Let us know if that work

Re: Removing Time Stamp From Date

Thu, 23 Oct 2014 08:52:19 GMT

Hi Gabriel

You asked to let you know if it works. It does but only when applied to the calendar statement DATE(Floor(WeekEnd(TempDate)),'DD/MM/YYYY')AS WeekEnd .

When applied to the preload it did not. I will add this to the Community to share the result

Many thanks,

Gareth

Re: Removing Time Stamp From Date

Thu, 23 Oct 2014 09:06:18 GMT

Thanks for all the replies which all used same approach. However, for information please note that it only worked when applied to the calendar statement, ie

Date(Floor(WeekEnd(TempDate))''DD/MM/YYYY') As WeekEnd

When applied to the preload and it did not make a difference to the error I was experiencing.

Thanks to all