topic Re: Subtract next row with the current row ?? in App Development

Subtract next row with the current row ??

gauravgg — Mon, 28 Nov 2016 12:43:03 GMT

Hi ,

in the following table where the Status_id = 4 i have to calculate the Result Cloumn by subtracting the update_date_time

highlighted in Orange- update_date_time in Green in HH:MM:SS

id_pk	Result Column(HH:MM:SS)	Status id	update_date_time
56327		1	2016-07-26 11:47:36
56328		2	2016-07-26 11:47:59
56330		3	2016-07-26 11:48:30
56331	2016-07-26 11:49:29 - 2016-07-26 11:48:41=00:00:48	4	2016-07-26 11:48:41
56332		4	2016-07-26 11:49:29
56334		3	2016-07-26 11:51:20

Mon, 28 Nov 2016 12:50:39 GMT

I'd use Peek() in load script. Peek()

gauravgg — Mon, 28 Nov 2016 17:15:30 GMT

Can u show with example...or share qvf

rwunderlich — Mon, 28 Nov 2016 17:40:09 GMT

Try the below() function. Something like:

if(Status_id=4, below(update_date_time) - update_date_time, '')

-Rob

swuehl — Mon, 28 Nov 2016 17:41:10 GMT

You can use Peek(), but Peek() can only address rows already read in, not rows that are located in following records.

You can change the sort order of your table to overcome this:

INPUT:

LOAD id_pk,

[Result Column(HH:MM:SS)],

[Status id],

update_date_time

FROM

(html, codepage is 1252, embedded labels, table is @1);

Result:

LOAD *,

If(Peek('Status id') = 4 and [Status id] = 4,interval(Peek('update_date_time')- update_date_time )) as test

Resident INPUT

order by id_pk desc;

DROP TABLE INPUT;