Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Use Hour()
Or you can try Timestamp(timestamp#(fieldname,'DD-MM-YYYY hh:mm:ss.fffffffff' TT),'hh')
Regards
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
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
Hi,
Change it to
Timestamp(timestamp#(fieldname,'DD-MMM-YY hh:mm:ss.fffffffff TT'),'hh')
Regards
Hi,
You can try with Hour ()
like
Hour(timestamp#(fieldname,'DD-MM-YYYY hh:mm:ss.fffffffff TT')
Regards
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
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
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