Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Re: Date and Time to Time Only

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

Use this line to load the field as time

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

Let me know if not works.

Highlighted
Employee
Employee

Re: Date and Time to Time Only

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

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);

Not applicable

Re: Date and Time to Time Only

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

MVP
MVP

Re: Re: Date and Time to Time Only

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

Not applicable

Re: Date and Time to Time Only

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

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);

QlikCommunity_Thread_122216_Pic1.JPG.jpg

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

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

Not applicable

Re: Date and Time to Time Only

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

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.. ?