Time in QlikView

[roee1983]

Dear all,

i have this table :

LOAD * INLINE [
id, time
1, 13:00:00
2, 13:01:01
3, 13:02:02
4, 13:04:03
5, 13:09:04
6, 13:11:05
7, 13:14:06
8, 13:15:07
9, 13:16:08
10, 13:18:09
11, 13:19:10
12, 13:20:11
13, 13:23:12
14, 13:25:13
15, 13:27:14
16, 13:32:15
17, 13:36:16
18, 13:39:17
19, 13:42:18
20, 13:48:18
];

what i need to get is only the records bwtween :

((first record )+ (x  'mm') +( y  'ss') ) to     ((last record) - (z  'mm') -(k 'ss') )

Thanks 

Roy

[tresesco · Accepted solution]

Try like:

Let x=10;
Let y=20;
Let z=30;
Let k=40;

Input:
LOAD *
		INLINE [
id, time
1, 13:00:00
2, 13:01:01
3, 13:02:02
4, 13:04:03
5, 13:09:04
6, 13:11:05
7, 13:14:06
8, 13:15:07
9, 13:16:08
10, 13:18:09
11, 13:19:10
12, 13:20:11
13, 13:23:12
14, 13:25:13
15, 13:27:14
16, 13:32:15
17, 13:36:16
18, 13:39:17
19, 13:42:18
20, 13:48:18
];

Let vFirstTime=Peek('time',0);
Let vLastTime=Peek('time',-1);

Let vMinTime=vFirstTime +Time#($(x), 'mm')+Time#($(y), 'ss');
Let vMaxTime=vLastTime -Time#($(z), 'mm')-Time#($(k), 'ss');

NoConcatenate
Output:
Load * Resident Input Where time >= $(vMinTime) AND   time <= $(vMaxTime) ;
                      
Drop table Input;