topic Re: Hours in QlikView

Hours

Mon, 13 Jul 2015 04:12:10 GMT

Hi All ,

Below is my data

SYSTEM - 01/04/2015 01:28:51 PM#

SYSTEM - 01/04/2015 01:49:07 PM#

SYSTEM - 01/04/2015 02:05:21 PM#

SYSTEM - 01/04/2015 02:09:13 PM#

SYSTEM - 01/04/2015 02:09:38 PM#

SYSTEM - 01/04/2015 02:13:22 PM#

In the above data , I have to take only the hour and min .

The Time has to converted to 24 Hours Format.

Need input on this

Re: Hours

sunny_talwar — Mon, 13 Jul 2015 04:16:49 GMT

Try this:

Time(TimeStamp#(Mid('SYSTEM - 01/04/2015 01:28:51 PM#', 10, 22), 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm') as Time

Tried it in a text box and got the following output

Re: Hours

sunny_talwar — Mon, 13 Jul 2015 04:21:27 GMT

This should also work

=Time(Time#(Mid('SYSTEM - 01/04/2015 01:28:51 PM#', 22, 10), 'hh:mm:ss TT'), 'hh:mm')

Re: Hours

Mon, 13 Jul 2015 04:21:31 GMT

LOAD

Time_Stamp,

Timestamp(Timestamp#(Trim(Mid(Time_Stamp,20,12)),'hh:mm:ss TT'),'hh:mm ') as H24_MM;

Temp:

LOAD * INLINE [

Time_Stamp

SYSTEM - 01/04/2015 01:28:51 PM#

SYSTEM - 01/04/2015 01:49:07 PM#

SYSTEM - 01/04/2015 02:05:21 PM#

SYSTEM - 01/04/2015 02:09:13 PM#

SYSTEM - 01/04/2015 02:09:38 PM#

SYSTEM - 01/04/2015 02:13:22 PM#

];

Re: Hours

settu_periasamy — Mon, 13 Jul 2015 04:30:12 GMT

One more..

Load Field,
Time(Trim(SubField(SubField(Field,'-',2),'#',1)),'HH.MM') as Hours;
LOAD * INLINE [
    Field
    SYSTEM - 01/04/2015 01:28:51 PM#
    SYSTEM - 01/04/2015 01:49:07 PM#
    SYSTEM - 01/04/2015 02:05:21 PM#
    SYSTEM - 01/04/2015 02:09:13 PM#
    SYSTEM - 01/04/2015 02:09:38 PM#
    SYSTEM - 01/04/2015 02:13:22 PM#
];

Re: Hours

Mon, 13 Jul 2015 04:30:58 GMT

sunindia , You are always faster than me . However, in the above mid function, start parameter 22 may not work if the hour number is 10, 11 or 12.

Regards,

KKR

Re: Hours

mohammadkhatimi — Mon, 13 Jul 2015 04:31:04 GMT

Time(TimeStamp#(Mid('SYSTEM - 01/04/2015 01:28:51 PM#', 10, 22), 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm') as HrMin

Hope this will help u...

Regards,

Mohammad

Re: Hours

jagan — Mon, 13 Jul 2015 04:38:29 GMT

Hi,

Try this sample script

Data:

LOAD

DatTime,

Timestamp(Timestamp#(Replace(DatTime,'SYSTEM - ', ''),'MM/DD/YYYY hh:mm:ss TT#'),'hh:mm') as Time;

Temp:

LOAD * INLINE [

DatTime

SYSTEM - 01/04/2015 01:28:51 PM#

SYSTEM - 01/04/2015 01:49:07 PM#

SYSTEM - 01/04/2015 02:05:21 PM#

SYSTEM - 01/04/2015 02:09:13 PM#

SYSTEM - 01/04/2015 02:09:38 PM#

SYSTEM - 01/04/2015 02:13:22 PM#

];

Regards,

Jagan.

Re: Hours

maxgro — Mon, 13 Jul 2015 05:04:22 GMT

if you only want the time part use frac (bold)

LOAD

TimeStamp,

timestamp(Timestamp#(subfield(TimeStamp,' - ',2),'DD/MM/YYYY hh:mm:ss TT#'), 'hh:mm') as DateTime_hhmm, // date and time hh:mm

time(frac(Timestamp#(subfield(TimeStamp,' - ',2),'DD/MM/YYYY hh:mm:ss TT#')), 'hh:mm') as Time_hhmm; // time hh:mm

LOAD * INLINE [

TimeStamp

SYSTEM - 01/04/2015 01:28:51 PM#

SYSTEM - 01/04/2015 01:49:07 PM#

SYSTEM - 01/04/2015 02:05:21 PM#

SYSTEM - 01/04/2015 02:09:13 PM#

SYSTEM - 01/04/2015 02:09:38 PM#

SYSTEM - 01/04/2015 02:13:22 PM#

];

Re: Hours

Mon, 13 Jul 2015 05:21:28 GMT

Hi,

Please find the attached QVW file

Re: Hours

sunny_talwar — Mon, 13 Jul 2015 10:14:23 GMT

Hahahaha ya I experience the same problem with some other users . But I don't see why it would be a problem with hours 10, 11 or 12?

Re: Hours

sunny_talwar — Mon, 13 Jul 2015 10:40:22 GMT

I see what you mean. Just need to change from where and to how many:

=Time(Time#(Mid('SYSTEM - 01/04/2015 11:28:51 PM#', 21, 11), 'hh:mm:ss TT'), 'hh:mm')

Good eye Kranthikumar

Best,

Sunny