topic Re: Timestamp in QlikView

Timestamp

Wed, 09 Jul 2014 06:21:56 GMT

Morning All,

Could anyone assist with converting the time stamp in the attached document to a date.

Thanks

Re: Timestamp

sujeetsingh — Wed, 09 Jul 2014 06:25:04 GMT

try this

Converting Timestamp into Date

Converting a timestamp to a date

Re: Timestamp

pradnyat — Wed, 09 Jul 2014 06:25:46 GMT

Do you want to extract the date from timestamp or you want to change the data type?

Re: Timestamp

Wed, 09 Jul 2014 06:29:41 GMT

Hi Herbert,

floor(date(dateField,'DD/MM/YYYY')) as newDate

if it is in text format then,

floor(date(date#(DateField,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY')) as newDate

Re: Timestamp

Wed, 09 Jul 2014 06:38:10 GMT

Hi Pradnya,

I need to extract the date. Please see the attached Excel file for the raw data.

Thank you

Re: Timestamp

its_anandrjs — Wed, 09 Jul 2014 06:41:39 GMT

Try like

Load

[Time Stamp],

Date( Time#([Time Stamp],'MM/DD/YYYY h:mm:ss TT'),'MM/DD/YYYY') as Date

From Location;

Re: Timestamp

Wed, 09 Jul 2014 06:46:41 GMT

Thanks Anand,

This is the closest answer so far, as it actually produces a date. Problem however is that its giving dates in the year 1899. My data is 2014 data.

Any suggestions?

Thanks

Re: Timestamp

Wed, 09 Jul 2014 06:50:29 GMT

Try this,

floor(date(date#([Time Stamp],'DD/MM/YYYY hh:mm:ss TT'),'DD/MM/YYYY')) as newDate

Change num to date from properties>Number>Date

Re: Timestamp

its_anandrjs — Wed, 09 Jul 2014 06:52:36 GMT

Use this script

LOAD

[Time Stamp],

Date(Timestamp#([Time Stamp],'MM/DD/YYYY h:mm:ss TT'),'MM/DD/YYYY') as Date

FROM

[Time Stamp.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Timestamp

amit_saini — Wed, 09 Jul 2014 06:55:42 GMT

Please see the attachment.

Thanks,
AS

Re: Timestamp

vardhancse — Wed, 09 Jul 2014 06:58:43 GMT

when we have a field which contains date and time:

Date:

Floor(Date(field)) as Field_Date;

Year:

Floor(Year(Field)) as Field_Year;

Time:

Floor(Time(Field)) as Field_Time;

Re: Timestamp

Wed, 09 Jul 2014 06:59:53 GMT

Hi Anand & Harshal,

Thank you both for your contributions, I have a solution.

Anand, you were the first to spot the need for the 'TT' in all the responses, however, Harshal thanks for pointing out that when you floor the date it reverts to a numeric number like 41477, and thus the need for properties>number>date.

Much appreciated

Re: Timestamp

rido1421 — Wed, 09 Jul 2014 07:02:00 GMT

Month(Time Stamp) as Month

Day(Time Stamp) as Day

Date(Time Stamp),'MM/DD/YY' as Date

Re: Timestamp

maxgro — Wed, 09 Jul 2014 07:04:28 GMT

Directory;

LOAD

[Time Stamp],

Date( Timestamp#([Time Stamp],'MM/DD/YYYY hh:mm:ss TT'),'MM/DD/YYYY hh:mm:ss') as Date

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

Re: Timestamp

its_anandrjs — Fri, 11 Jul 2014 19:20:18 GMT

Thanks, and welcome glad to help you.

Regards,

Anand