Peek()

[#1]

Hello All,

I have the below data file. I have to find out the difference in the stock from the last reading of the previous date to the first reading of the next day.

Date	Product Code	Time	Stock
28-01-2015	1600	12:00:00	1324
28-01-2015	1700	12:00:00	1745
28-01-2015	1800	12:00:00	0
28-01-2015	1600	18:00:00	1424
28-01-2015	1700	18:00:00	1845
28-01-2015	1800	18:00:00	10
28-01-2015	1600	23:30:00	1624
28-01-2015	1700	23:30:00	1924
28-01-2015	1800	23:30:00	30
29-01-2015	1600	01:00:00	1614
29-01-2015	1700	01:00:00	1915
29-01-2015	1800	01:00:00	30
29-01-2015	1600	23:59:59	2451
29-01-2015	1700	23:59:59	2784
29-01-2015	1800	23:59:59	56
30-01-2015	1600	12:30:00	2461
30-01-2015	1700	12:30:00	2784
30-01-2015	1800	12:30:00	55

So my resultant table should look something like:

Date	Product Code	Difference
28-01-2015	1600	-
28-01-2015	1700	-
28-01-2015	1800	-
29-01-2015	1600	10 (1624-1614)
29-01-2015	1700	9 (1924-1915)
29-01-2015	1800	0 (30-30)
30-01-2015	1600	-10 (2451-2461)
30-01-2015	1700	0 (2784-2784)
30-01-2015	1800	1 (56-55)

I have tried using the peek function but couldn't combine it to the max,min(Time) of the day to get the difference

Regards

[lironbaram · Accepted solution]

this script should do the trick

DataTemp:

LOAD

    "Date",

    "Product Code",

    "Time",

    Stock

FROM [lib://Comm/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Data:

load *,

    if(Previous("Product Code")="Product Code" and "Date" <> Previous("Date"), Previous(Stock)-Stock) As StockDelta

Resident DataTemp

Order By "Product Code","Date","Time";

drop Table DataTemp;

also attach is a demo app

[#3]

Thanks Liron. This helped