cancel
Showing results for 
Search instead for 
Did you mean: 

cumulative sum by symbol, but with a cap on the value

KdbNoob
New Contributor

For example in the below table, I want to run the cumulative sum on "val" column of the table, grouped by the symbol column. but I want to cap the cumulative sum by the value in the "cap" column. If the cumulative sum exceeds the cap, I just cap it at that value. And for the next value, I will add it on up of the capped value:


Example  I am given input date, sym, val, cap, I want to produce the output in "cumval" column.

date sym val cap cumval
2020.01.01 AAPL 100 200 100
2020.01.02 AAPL 100 200 200
2020.01.03 AAPL 100 200 200
2020.01.04 AAPL -100 200 100
2020.01.01 MSFT 100 300 100
2020.01.02 MSFT 100 300 200
2020.01.03 MSFT 100 300 300
2020.01.04 MSFT 100 400 400


for example on 2020.01.03, cumval of AAPL is capped at 200. and on 2020.01.04, we have 200-100 = 100 
1 REPLY 1

alvi_kabir919
New Contributor
q)update cumval:{min z,x+y}\[0;val;cap] by sym from t
date       sym  val  cap cumval
-------------------------------
2020.01.01 AAPL 100  200 100
2020.01.02 AAPL 100  200 200
2020.01.03 AAPL 100  200 200
2020.01.04 AAPL -100 200 100
2020.01.01 MSFT 100  300 100
2020.01.02 MSFT 100  300 200
2020.01.03 MSFT 100  300 300
2020.01.04 MSFT 100  400 400