QV Script arithmetic problem

[slribeiro]

Greetings,

imagine the following table

Date           | Quantity | QtdStock

15/02/2015  | 180        | 200

18/02/2015  | 80          | 200

1/02/2015    | 150        | 200

16/02/2015  | 100        | 200

I need to create a field that has the values of the remaining quantities in stock knowing that the products are removed from stock by date so the final table should be something like this:

Date           | Quantity | StockTotal | Remaining Stock

15/02/2015  | 180        | 200            |     20

18/02/2015  | 80          | 200            |     80

1/02/2015    | 150        | 200            |     0

16/02/2015  | 100        | 200            |     100

Hope you can help me and thank you in advance.

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD * Inline [

Date          | Quantity | QtdStock

15/02/2015  | 180        | 200

18/02/2015  | 80          | 200

1/02/2015    | 150        | 200

16/02/2015  | 100        | 200

] (delimiter is |);

FinalTable:

LOAD *,

  If(QtdStock >= CumQuantity, Quantity,

  If(Temp >=0, QtdStock - Quantity, 0)) as [Remaining Stock];

LOAD *,

  RangeSum(Peek('QtdStock'), -Peek('CumQuantity')) as Temp;

LOAD *,

  RangeSum(Peek('CumQuantity'), Quantity) as CumQuantity

Resident Table

Order By Date desc;

DROP Table Table;