topic Re: help for desuming Time in QlikView

help for desuming Time

Thu, 05 Jul 2012 08:52:19 GMT

Hi all,

i have a structure like this:

04/07/2012				10:06:47
04/07/2012				13:45:34
04/07/2012				14:42:54
04/07/2012				19:20:01
03/07/2012				14:14:12
03/07/2012				19:06:40
02/07/2012				12:54:03
02/07/2012				13:54:13
02/07/2012				18:46:19
28/06/2012				19:35:49
28/06/2012				9:49:10
28/06/2012				13:32:18
28/06/2012				14:41:18
27/06/2012				13:42:58
27/06/2012				14:26:25
27/06/2012				9:15:08
27/06/2012				19:16:15

and i would to load this structure in somethings like this:

date	entry	exit	time
04/07/2012	13:45:34	19:20:01	5:34:27
03/07/2012	14:14:12	19:06:40	4:52:28
02/07/2012	12:54:03	18:46:19	5:52:16
28/06/2012	09:49:10	19:35:49	9:46:39
27/06/2012	09:15:08	19:16:15	10:01:07

where entry is the min and the exit is the max of each day and time is the difference in h:mm:ss.

Can someone help me?

thanks!

Re: help for desuming Time

Anonymous — Thu, 05 Jul 2012 09:04:24 GMT

Here you go:

T1:

Load * Inline [

Date, Time

04/07/2012,10:06:47

04/07/2012,13:45:34

04/07/2012,14:42:54

04/07/2012,19:20:01

03/07/2012,14:14:12

03/07/2012,19:06:40

02/07/2012,12:54:03

02/07/2012,13:54:13

02/07/2012,18:46:19

28/06/2012,19:35:49

28/06/2012,9:49:10

28/06/2012,13:32:18

28/06/2012,14:41:18

27/06/2012,13:42:58

27/06/2012,14:26:25

27/06/2012,9:15:08

27/06/2012,19:16:15

];

T2:

Load

Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Date,

Time(Min(Time),'hh:mm:ss') as StartTime,

Time(Max(Time),'hh:mm:ss') as EndTime,

Interval(Max(Time)-Min(Time),'hh:mm:ss') as TimeDiff

Resident T1

group by Date;

Drop table T1;

Re: help for desuming Time

Thu, 05 Jul 2012 09:49:59 GMT

Great!! It work very fine!

Now i'm complicating the structure becouse i would subtrac the time of lunch that is in the middle of the date: 13:45 to 14:42.

04/07/2012,10:06:47

04/07/2012,13:45:34

04/07/2012,14:42:54

04/07/2012,19:20:01

I'm trying, but this is my first app in qlickview...

Thank you!!