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