I have a table as below for which I have to determine the buySellRatio of notional based on the side grouped by ratings and dt.
// Sample Input -
dt ratings side notional
--------------------------------
2025.04.04 AAA B 10
2025.04.04 BBB S 20
2025.04.04 AAA S 30
2025.04.04 BBB B 40
2025.04.05 AAA S 50
2025.04.05 BBB B 60
2025.04.05 BBB S 70
2025.04.05 AAA B 80
2025.04.05 AAA S 90
// Code to generate sample input -
t:([] dt:2025.04.04 2025.04.04 2025.04.04 2025.04.04 2025.04.05 2025.04.05 2025.04.05 2025.04.05 2025.04.05;
ratings: `AAA`BBB`AAA`BBB`AAA`BBB`BBB`AAA`AAA;
side: `B`S`S`B`S`B`S`B`S;
notional:10*1+til 9
);
//Expected Output
dt ratings buySellRatio
-------------------------------
2025.04.04 AAA 0.3333333
2025.04.04 BBB 2
2025.04.05 AAA 0.5714286
2025.04.05 BBB 0.8571429
Unsuccessful Attempt --> Referring to the pivot tables description of kdb - https://code.kx.com/q/kb/pivoting-tables/ I could come up with below solution, however it works for only a single date at a time.
.up.tab:select sum notional by dt, ratings, side from t;
.up.ratings:asc exec distinct side from .up.tab;
.up.pvt:0!exec .up.ratings#(side!notional) by ratings:ratings from .up.tab;
select ratings, buySellRatio:B%S from .up.pvt
Another way to handle the inner grouping:
q)exec ratio:(%).(sum each notional group side)`B`S by dt,ratings from t
dt ratings| ratio
------------------| ---------
2025.04.04 AAA | 0.3333333
2025.04.04 BBB | 2
2025.04.05 AAA | 0.5714286
2025.04.05 BBB | 0.8571429
or (slightly easier to read)
exec ratio:{x[`B]%x`S}sum each notional group side by dt,ratings from t