Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Peek()

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.

DateProduct CodeTimeStock
28-01-2015160012:00:00 1324
28-01-2015170012:00:001745
28-01-2015180012:00:000
28-01-2015160018:00:001424
28-01-2015170018:00:001845
28-01-2015180018:00:0010
28-01-2015160023:30:001624
28-01-2015170023:30:001924
28-01-2015180023:30:0030
29-01-2015160001:00:001614
29-01-2015170001:00:001915
29-01-2015180001:00:0030
29-01-2015160023:59:592451
29-01-2015170023:59:592784
29-01-2015180023:59:5956
30-01-2015160012:30:002461
30-01-2015170012:30:002784
30-01-2015180012:30:0055

So my resultant table should look something like:

DateProduct CodeDifference
28-01-20151600-
28-01-20151700-
28-01-20151800-
29-01-2015160010 (1624-1614)
29-01-201517009 (1924-1915)
29-01-201518000 (30-30)
30-01-20151600-10 (2451-2461)
30-01-201517000 (2784-2784)
30-01-201518001 (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

1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

Re: Peek()

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

2 Replies
lironbaram
Honored Contributor II

Re: Peek()

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

Not applicable

Re: Peek()

Thanks Liron. This helped