How to Subtract Date from the previous row????

[gauravgg]

Hi

I have the following table

I have to create a New Column

I have to subtract RowNo 2 from RowNo 1 for which the result should come in new column in format (HH:MM:SS)

This should be applied for all the rows.

Timestamp	New Column(HH:MM:SS)	RowNo
08-01-2016 03:00		1
08-01-2016 03:01	(08-01-2016 03:01)-(08-01-2016 03:00)	2
08-01-2016 03:06		3
08-01-2016 03:06		4
08-01-2016 03:07		5
08-01-2016 03:07		6
08-01-2016 03:10		7
08-01-2016 03:11		8
08-01-2016 03:17		9
08-01-2016 03:19		10
08-01-2016 03:23		11
08-01-2016 03:23		12
08-01-2016 03:31		13
08-01-2016 03:34		14
08-01-2016 03:49		15
08-01-2016 03:51		16
08-01-2016 03:54		17

[sunny_talwar]

In the front end of the app, you can use Above or Below function:

Timestamp - Above(Timestamp)

[gauravgg]

Hi Sunny

If i an not wrong Timestamp - Above(Timestamp) can be done on the visualisation part

Can this be done in the script ?

[sunny_talwar]

You can use Peek/Previous in the script to do this. But make sure your data is correctly sorted when you use peek/previous.

Peek() or Previous() ?

[gauravgg]

it would be help full , if you share script

or Share .qvf

[sunny_talwar]

Something like this:

SET TimestampFormat='MM-DD-YYYY hh:mm';

Table:

LOAD Timestamp,

  RowNo

FROM

[https://community.qlik.com/thread/239834]

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

FinalTable:

LOAD RowNo,

  Timestamp,

  Interval(Timestamp - Previous(Timestamp), 'hh:mm:ss') as NewColumn

Resident Table

Order By RowNo;

DROP Table Table;