Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

need to convert datetime into hr .

Hi All,

Please find the attached excel for data.I am looking for similar o/p in Qlikview as show in image .

Could you please help.

I am finding issue converting date time into hour.

Thanks,

Rahul

8 Replies
PrashantSangle

Hi,

Use Hour()

Or you can try Timestamp(timestamp#(fieldname,'DD-MM-YYYY hh:mm:ss.fffffffff' TT),'hh')

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable
Author

Hi Max,

I tired using this formula Timestamp(timestamp#(fieldname,'DD-MM-YYYY hh:mm:ss.fffffffff' TT),'hh') its not working.

Could you please suggest

tresesco
MVP
MVP

Timestamp() is a formatting function which doesn't change the original timestamp value. You better use Hour() like:

Hour(timestamp#(fieldname,'DD-MMM-YYYY hh:mm:ss.fffffffff TT')) as Hour

PrashantSangle

Hi,

Change it to

Timestamp(timestamp#(fieldname,'DD-MMM-YY hh:mm:ss.fffffffff TT'),'hh')

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
PrashantSangle

Hi,

You can try with Hour ()

like

Hour(timestamp#(fieldname,'DD-MM-YYYY hh:mm:ss.fffffffff TT')

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
ashfaq_haseeb

Hi,

Use below code

load *,hour(Date) as Hour;

LOAD FILE_SUBJECT_CD,

    Time,

      Timestamp(Timestamp#(Time,'DD-MMM-YY hh.mm.ss.ffffffff TT')) as Date,

      Timestamp(Timestamp#(Time,'DD-MMM-YY hh.mm.ss.ffffffff TT'),'hh') as Hour1

FROM

[Sample (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Have a look at attached application.

Regards

ASHFAQ

rbecher
Luminary Alumni
Luminary Alumni

Hi Rahul,

if I understand it right you wnat hours from 1-12:

Data:

LOAD FILE_SUBJECT_CD,

    Timestamp(Timestamp#(Time,'DD-MMM-YY hh.mm.ss.ffffffff TT')) as DateTime,

    Num(Left(Time(Timestamp#(Time,'DD-MMM-YY hh.mm.ss.ffffffff TT'),'HH TT'),2)) as Hour

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

See also attached..

- Ralf

Vizlib Head of R&D
MarcoWedel

Hi,

one possible solution:

QlikCommunity_Thread_131895_Pic1.JPG.jpg

LOAD *,

     Hour(Time) as Hour;

LOAD FILE_SUBJECT_CD,

     Timestamp(Timestamp#(Time, 'DD-MMM-YY hh.mm.ss.fffffffff TT')) as Time

FROM [http://community.qlik.com/servlet/JiveServlet/download/601381-123320/Sample.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco