topic Calculation time spent between multiple timestamps in QlikView

Calculation time spent between multiple timestamps

BusinessCo — Sat, 16 Nov 2024 18:27:03 GMT

Hi,

I have data with multiple timestamp pro day. What i need is to calculate the time between first en last stamp pro day. In example below the tme between 07:04:09 and 14:31: 49 is 07:27:40.

Can somebody help me? Thanx!

License Plate	Time	Number of emptyings
59-BDS-6	07:04:09	1
59-BDS-6	07:51:15	1
59-BDS-6	08:06:26	1
59-BDS-6	08:41:22	1
59-BDS-6	09:56:20	1
59-BDS-6	09:59:08	1
59-BDS-6	10:59:29	1
59-BDS-6	10:59:50	1
59-BDS-6	11:24:30	1
59-BDS-6	11:25:40	1
59-BDS-6	13:56:40	1
59-BDS-6	13:59:48	1
59-BDS-6	14:23:36	1
59-BDS-6	14:31:49	1

The result must be:

License Plate	Total time spent	Number of emptyings
59-BDS-6	07:27:40	14

Re: Calculation time spent between multiple timestamps

tresB — Wed, 24 Jun 2020 11:10:41 GMT

Time(max(Time#(Time, 'hh:mm:ss'))-Min(Time#(Time, 'hh:mm:ss')), 'hh:mm:ss')

Re: Calculation time spent between multiple timestamps

Taoufiq_Zarra — Wed, 24 Jun 2020 11:14:10 GMT

One solution :

Data: LOAD * INLINE [ License Plate, Time, Number of emptyings 59-BDS-6, 07:04:09, 1 59-BDS-6, 07:51:15, 1 59-BDS-6, 08:06:26, 1 59-BDS-6, 08:41:22, 1 59-BDS-6, 09:56:20, 1 59-BDS-6, 09:59:08, 1 59-BDS-6, 10:59:29, 1 59-BDS-6, 10:59:50, 1 59-BDS-6, 11:24:30, 1 59-BDS-6, 11:25:40, 1 59-BDS-6, 13:56:40, 1 59-BDS-6, 13:59:48, 1 59-BDS-6, 14:23:36, 1 59-BDS-6, 14:31:49, 1 ]; output: load [License Plate],sum([Number of emptyings]) as [Number of emptyings], Interval ( (time(max(Time))) -(time(min(Time))) , 'hh:mm:ss' ) as [Total time spent] resident Data group by [License Plate]; drop table Data;

output :

Re: Calculation time spent between multiple timestamps

NitinK7 — Wed, 24 Jun 2020 11:24:19 GMT

Hi,

try like below

dimension- License Plate

Expression_1(Total time spent)- Time(FirstSortedValue(Time,-Time)-FirstSortedValue(Time,Time),'hh:mm:ss').

Expression_2(Number of emptyings)- count([Number of emptyings]).

See below screenshot

Thanks

Nitin.

Re: Calculation time spent between multiple timestamps

BusinessCo — Wed, 24 Jun 2020 11:32:10 GMT

Thank you very much!