Getting the next row and calculate.

[#1]

Hi,

I am trying to use QV script to derive the RunInterval from the given RunIntervalStartTime. How can i achieve this?

Column/ Row	A	B	C	D
1	Runner ID (Given)	RunDate (Given)	RunIntervalStartTime (Given)	RunInterval (To Calculate)	LOGIC
2	1	15/07/2015	14:30:00	0.00063657	C3 - C2
3	1	15/07/2015	14:30:55	0.000625	C4 - C3
4	1	15/07/2015	14:31:49	Null()	Null()
5	1	21/07/2015	15:30:00	0.00056713	C6 - C5
6	1	21/07/2015	15:30:49	0.00074074	C7 - C6
7	1	21/07/2015	15:31:53	Null()	Null()
8	2	15/07/2015	14:30:00	0.00053241	C9 - C8
9	2	15/07/2015	14:30:46	0.00061343	C10 - C9
10	2	15/07/2015	14:31:39	Null()	Null()
11	2	21/07/2015	15:30:00	0.000625	C12 - C11
12	2	21/07/2015	15:30:54	0.0005787	C13 - C12
13	2	21/07/2015	15:31:44	Null()	Null()

As my data does not follow an order, i am putting them in order first.

My pseudo code logic is

1. Order by RunnerID, RunDate, RunIntervalStartTime
2. Find difference between RunIntervalStartTime first row versus the next row, if RunnerID and RunDate is the same as the next row
3. If RunnerID and RunDate is NOT the same as the next row, return Null()

VIOLA!

I have tried the script below. However, I need help for the IF condition. Appreciate any help.

======

[Run]:

LOAD * INLINE

[

Runner ID	RunDate	RunIntervalStartTime
1,	15/07/2015,	14:30:00
1,	15/07/2015,	14:30:51
1,	15/07/2015,	14:31:44
1,	21/07/2015,	15:30:00
1,	21/07/2015,	15:30:48
1,	21/07/2015,	15:31:33
2,	15/07/2015,	14:30:00
2,	15/07/2015,	14:30:59
2,	15/07/2015,	14:31:52
2,	21/07/2015,	15:30:00
2,	21/07/2015,	15:30:57
2,	21/07/2015,	15:31:57

];

[Run1]:

Noconcatenate LOAD *,

  If(RunDate = peek( 'RunDate', 1) , 'Test' ,Null()) as RunInterval

Resident Run Order by RunnerID, RunDate, [RunStartTime];

DROP Table Run;

======

[swuehl · Accepted solution]

You can't address a record in a following line using Peek(), but you can sort your table accordingly (time descending).

Also take care of correctly spelled field names.

[Run]:

LOAD * INLINE

[

Runner ID, RunDate, RunIntervalStartTime

1, 15/07/2015, 14:30:00

1, 15/07/2015, 14:30:51

1, 15/07/2015, 14:31:44

1, 21/07/2015, 15:30:00

1, 21/07/2015, 15:30:48

1, 21/07/2015, 15:31:33

2, 15/07/2015, 14:30:00

2, 15/07/2015, 14:30:59

2, 15/07/2015, 14:31:52

2, 21/07/2015, 15:30:00

2, 21/07/2015, 15:30:57

2, 21/07/2015, 15:31:57

];

[Run1]:

Noconcatenate LOAD *,

  If(RunDate = peek( 'RunDate') and [Runner ID] = Peek('Runner ID') , Peek('RunIntervalStartTime')-RunIntervalStartTime ,Null()) as RunInterval

Resident Run Order by [Runner ID], RunDate, [RunIntervalStartTime] desc;

DROP Table Run;