topic Re: Date and Time to Time Only in QlikView

Date and Time to Time Only

Tue, 17 Jun 2014 09:58:00 GMT

Help nothing works. I want to convert a date and time field to time only

The field I'm converting always looks like this with dates as 00/01/1900 with the time after ie

00/01/1900 00:01:03

00/01/1900 10:05:13

00/01/1900 15:29:22

00/01/1900 20:44:52

nothing seems to extract the time only any ideas

Re: Date and Time to Time Only

tresB — Tue, 17 Jun 2014 09:59:58 GMT

Use Frac(). Like:

LOAD Time(Frac(Time),'hh:mm:ss') as Time

FROM <>;

Re: Date and Time to Time Only

its_anandrjs — Tue, 17 Jun 2014 10:01:34 GMT

Load like and try one of this

Load

Time,

Timestamp#(Time,'M/D/YYYY h:mm:ss TT') as NewTime

Time(Time,'hh:mm:ss') as NewTime2,

Time(Time,'hh:mm:ss TT') as NewTime3

From location;

And it seems in excel file your Time field is not formatted as the proper Time or Date field try one of this.

Re: Date and Time to Time Only

ashfaq_haseeb — Tue, 17 Jun 2014 10:09:24 GMT

If it is date field

then use

=Time(DATE_TIME_Field)

Regards

ASHFAQ

Re: Date and Time to Time Only

thomas_skariah — Tue, 17 Jun 2014 10:09:44 GMT

Hi,

PFA, you can get help from this.

Regards,

Tom

Re: Date and Time to Time Only

Tue, 17 Jun 2014 10:14:22 GMT

Doesn't work it just returns " - "

Re: Date and Time to Time Only

its_anandrjs — Tue, 17 Jun 2014 10:17:51 GMT

Try one of this

Time(Time,'hh:mm:ss TT') as NewTime1,

Time(Time,'hh:mm:ss') as NewTime2,

Time(Time,'hh:mm:ss TT') as NewTime3

Re: Date and Time to Time Only

Tue, 17 Jun 2014 10:18:55 GMT

That the problem the formatting of the excel file seems strange but all the files come like this and I need an extraction which will put it right

Re: Re: Date and Time to Time Only

tresB — Tue, 17 Jun 2014 10:21:31 GMT

It worked with me. PFA

Re: Date and Time to Time Only

nlmmaurya — Tue, 17 Jun 2014 10:22:41 GMT

Hi,

Try this if your Date format would be always same.

LOAD Time,

Right(Time,11) as NewTime

FROM<>

Re: Date and Time to Time Only

ashfaq_haseeb — Tue, 17 Jun 2014 10:24:53 GMT

Hi look at the below code.

All of them working.

t1:

LOAD Time,

time(Time) as Time1,

Time(Frac(Time),'hh:mm:ss') as Time2,

Time(Time,'hh:mm:ss TT') as Time3,

Time(Time,'hh:mm:ss') as Time4,

Time(Time,'hh:mm:ss TT') as Time5

FROM

QVTime.xlsx

(ooxml, embedded labels, table is Sheet1);

Regards

ASHFAQ

Re: Date and Time to Time Only

its_anandrjs — Tue, 17 Jun 2014 10:26:01 GMT

Use this line to load the field as time

Time(Time,'hh:mm:ss TT') as TimeField

Let me know if not works.

Re: Date and Time to Time Only

nam — Tue, 17 Jun 2014 10:28:00 GMT

Hi if its a formatting problem then just use the subfield function that will get you the correct answer:

maketime(subfield(yourdatefield,':',4),subfield(yourdatefield,':',5),subfield(yourdatefield,':',6)) as Time

you can do that in the script and will work a treat

Re: Date and Time to Time Only

its_anandrjs — Tue, 17 Jun 2014 10:29:36 GMT

Try to load only this way

Load

Time,

Time(Time,'hh:mm:ss TT'),8),'hh:mm:ss') as NewTime1,

Time(Left(Time(Time,'hh:mm:ss TT'),8),'hh:mm:ss') as NewTime2 // Format you get hh:mm:ss

FROM

[QVTime.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date and Time to Time Only

Tue, 17 Jun 2014 10:49:31 GMT

Arrrg this is driving me nuts... it doesn't work?

Ive noticed that in excel when the file is opened only 00/01/1900 appears but when you double click the cell then the full date and time appears

ie 00/01/1900 15:29:22

Re: Re: Date and Time to Time Only

tresB — Tue, 17 Jun 2014 10:51:40 GMT

That is because of formatting. Actual underlaying data is having timstamp with time. See my attached qvw in above post.

Re: Date and Time to Time Only

Tue, 17 Jun 2014 10:54:58 GMT

Thanks so much for all the replies

For some reason my QlikView is showing personal edition and not licenced and so I cant open the QVW, Ive faulted the licence issue with our IT but its gonna take a little while to resolve, is there any thing you can post in here in the meantime.. ?

Re: Date and Time to Time Only

MarcoWedel — Tue, 17 Jun 2014 10:56:10 GMT

Hi Glenn,

using the time() function should be enough:

LOAD  Time(Time, 'hh:mm:ss') as Time
FROM [http://community.qlik.com/servlet/JiveServlet/download/550208-111495/QVTime.xlsx]
(ooxml, embedded labels, table is Sheet1);

If you can ensure that the date part of your excel data will always be 00/01/1900 then there is no need for any removing the integer part of your excel timestamp (as e.g. the proposed frac() function would do). The integer part of the numeric representation of your loaded timestamps would be 0 already.

hope this helps

regards

Marco

Re: Date and Time to Time Only

its_anandrjs — Tue, 17 Jun 2014 10:59:04 GMT

If you need time then change format in the excel as Time and save the excel file.

Re: Date and Time to Time Only

Tue, 17 Jun 2014 10:59:53 GMT

it doesn't work?

Ive noticed that in excel when the file is opened only 00/01/1900 appears but when you double click the cell then the full date and time appears

ie 00/01/1900 15:29:22