Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Getting the next row and calculate.

Hi,

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


     

Column/ RowABCD
1

Runner ID

(Given)

RunDate

(Given)

RunIntervalStartTime

(Given)

RunInterval

(To Calculate)

LOGIC
2115/07/201514:30:000.00063657C3 - C2
3115/07/201514:30:550.000625C4 - C3
4115/07/201514:31:49Null()Null()
5121/07/201515:30:000.00056713C6 - C5
6121/07/201515:30:490.00074074C7 - C6
7121/07/201515:31:53Null()Null()
8215/07/201514:30:000.00053241C9 - C8
9215/07/201514:30:460.00061343C10 - C9
10215/07/201514:31:39Null()Null()
11221/07/201515:30:000.000625C12 - C11
12221/07/201515:30:540.0005787C13 - C12
13221/07/201515:31:44Null()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 IDRunDateRunIntervalStartTime
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;

======

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

1 Reply
swuehl
MVP
MVP

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;