Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
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!!