Hi everyone,
I have to calculate the difference between the value of a field and that of the field in the previous row in the same column.
My data is an event log, I want to calculate differences between timestamps and the data is sorted by the timestamp column in ascending order.
In addition, I am looking for a function that works with filters or drill downs in the data set, as the events are categorised and I need to calculate the differences not simply between the rows but calculate the timestamp differences of events in the same group of categories.
Below table illustrates the data structure.
My target value is in Column E, with the expected categorised timestamp-differences. I assume there will be a NULL value whenever there is no previous row available.
As I have a set of close to 2 mill rows with more than 1,000 possible groups / category-combinations, manual splitting of the data by these groups and applying the function thereafter would not be practical.
Can anyone help?
Many thanks in advance,
Thomas
COL A COL B COL C COL D COL E
ID timestamp Cluster1 Cluster2 TimeInterval
ROW 1 1 19.10.2015 20:12:00 A x [NULL]
ROW 2 2 19.10.2015 20:16:00 A x B2-B1
ROW 3 3 19.10.2015 20:17:00 A x B3-B2
ROW 4 4 19.10.2015 20:22:00 B x [NULL]
ROW 5 5 19.10.2015 20:23:00 B x B5-B4
ROW 6 6 19.10.2015 20:25:00 B z [NULL]
ROW 7 7 19.10.2015 20:27:00 B z B7-B6
ROW 8 8 19.10.2015 20:29:00 B x B8-B5
ROW 9 9 19.10.2015 20:30:00 A z [NULL]
ROW 10 10 19.10.2015 20:32:00 A z B10-B9
ROW 11 11 19.10.2015 20:33:00 A z B11-B10
ROW 12 12 19.10.2015 20:34:00 A z B12-B11
ROW 13 13 19.10.2015 20:34:00 A z B13-B12
ROW 14 14 19.10.2015 20:38:00 A z B14-B13
ROW 15 15 19.10.2015 20:38:00 A x B15-B3
ROW 16 16 19.10.2015 20:39:00 A x B16-B15
ROW 17 17 19.10.2015 20:41:00 A x B17-B16
ROW 18 18 19.10.2015 20:42:00 B z B18-B7
ROW 19 19 19.10.2015 20:44:00 B z B19-B18
ROW 20 20 19.10.2015 20:46:00 A y [NULL]
ROW 21 21 19.10.2015 20:52:00 A y B21-B20
