need to convert datetime into hr .

[#1]

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

[PrashantSangle]

Hi,

Use Hour()

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

Regards

[#3]

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]

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

[PrashantSangle]

Hi,

You can try with Hour ()

like

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

Regards

[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]

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

[MarcoWedel]

Hi,

one possible solution:

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