topic Re: Convert multiple hh:mm:ss into hours in QlikView

Convert multiple hh:mm:ss into hours

saumyashah90 — Sat, 31 Oct 2015 08:29:35 GMT

For example I have time field

11:22:30

11:53:15

11:59:30

12:25:02

12:54:35

13:56:20

I should get output

3 at 11hrs

2 at 12hrs

1 at 13 hrs

Re: Convert multiple hh:mm:ss into hours

Anonymous — Sat, 31 Oct 2015 08:42:40 GMT

Maybe something like this ?

Temp01 :

LOAD * INLINE [

Time

11:22:30

11:53:15

11:59:30

12:25:02

12:54:35

13:56:20

];

NoConcatenate

Temp02 :

Load

Hour(Time) as Hour ,

;

Load

Time ( Time# ( Time ,'hh:mm:ss' ) ) as Time

resident Temp01

;

drop table Temp01 ;

load

Hour ,

Count(Hour) as CountHour

resident Temp02

group by Hour

order by Hour

;

drop table Temp02 ;

Re: Convert multiple hh:mm:ss into hours

saumyashah90 — Sat, 31 Oct 2015 08:54:19 GMT

Hi bill,

Thanks for the response but idon't wanna go with group by or order by....

With help of functions I can do I guess can you help me with that

Re: Convert multiple hh:mm:ss into hours

HirisH_V7 — Sat, 31 Oct 2015 09:00:22 GMT

Hi,

Temp:

LOAD * INLINE [Time,Old Time

11:22:30,11:22:30

11:53:15,11:53:15

11:59:30,11:59:30

12:25:02,12:25:02

12:54:35,12:54:35

13:56:20,13:56:20

];

Newinfo:

NoConcatenate

Load

Hour(Time) as Hour,

[Old Time]

Resident Temp;

Drop Table Temp;

Hope this Helps,

Thanks,

Hirish

Re: Convert multiple hh:mm:ss into hours

Anonymous — Sat, 31 Oct 2015 09:01:09 GMT

Why don't you want to use group by or order by.... ?

Re: Convert multiple hh:mm:ss into hours

saumyashah90 — Sat, 31 Oct 2015 09:11:16 GMT

There are millions of rows and numerous fields along with it.... If iload whole datable again doing no concatenate will create redundancy

Re: Convert multiple hh:mm:ss into hours

maxgro — Sat, 31 Oct 2015 09:11:33 GMT

maybe with a calculated dimension in a chart (str table, bar, etc..)

dimension

=Time(floor(t,1/24), 'hh')

Re: Convert multiple hh:mm:ss into hours

HirisH_V7 — Sat, 31 Oct 2015 09:17:20 GMT

Hi,

Temp:

LOAD * INLINE [Time,Old Time

11:22:30,11:22:30

11:53:15,11:53:15

11:59:30,11:59:30

12:25:02,12:25:02

12:54:35,12:54:35

13:56:20,13:56:20

];

Newinfo:

NoConcatenate

Load

Hour(Time) as Hour,

Time(Time, 'hh:mm TT') AS TimeAMPM,

Time(Time) AS Time24h,

[Old Time]

Resident Temp;

Drop Table Temp;

Hope This Helps .

Thanks,

Hirish

Re: Convert multiple hh:mm:ss into hours

Anonymous — Sat, 31 Oct 2015 09:19:03 GMT

Just use the count() function in the front end then.