topic Re: need to convert datetime into hr . in QlikView

need to convert datetime into hr .

Thu, 04 Sep 2014 06:59:04 GMT

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

Re: need to convert datetime into hr .

PrashantSangle — Thu, 04 Sep 2014 07:03:56 GMT

Hi,

Use Hour()

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

Regards

Re: need to convert datetime into hr .

Thu, 04 Sep 2014 07:07:35 GMT

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

Re: need to convert datetime into hr .

tresB — Thu, 04 Sep 2014 07:14:15 GMT

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

Re: need to convert datetime into hr .

PrashantSangle — Thu, 04 Sep 2014 07:16:52 GMT

Hi,

Change it to

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

Regards

Re: need to convert datetime into hr .

PrashantSangle — Thu, 04 Sep 2014 07:17:48 GMT

Hi,

You can try with Hour ()

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

Regards

Re: need to convert datetime into hr .

ashfaq_haseeb — Thu, 04 Sep 2014 07:20:48 GMT

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

Re: need to convert datetime into hr .

rbecher — Thu, 04 Sep 2014 07:38:23 GMT

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

Re: need to convert datetime into hr .

MarcoWedel — Thu, 04 Sep 2014 10:47:46 GMT

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