Hi Andrew,
Here’s an example with a simplified table to clarify how the update statements are working in the previous answer.
Given the following table, ‘tablewithpnl’:
q) tablewithpnltime sym pnl ------------------------00:00:00.577 sym1 00:00:08.938 sym1 -8.75 00:00:09.049 sym1 -9 00:00:19.881 sym1 -22.5 00:00:19.881 sym1 -25.5 00:00:29.049 sym2 00:00:39.881 sym2 50 00:00:49.881 sym2 -25
We can use an update statement to add column r, calculating the difference between consecutive PNLs by sym.
This gives the table below.
q) update r:deltas pnl by sym from tablewithpnl
time sym pnl r
--------------------------------
00:00:00.577 sym1
00:00:08.938 sym1 -8.75
00:00:09.049 sym1 -9 -0.25
00:00:19.881 sym1 -22.5 -13.5
00:00:19.881 sym1 -25.5 -3
00:00:29.049 sym2
00:00:39.881 sym2 50
00:00:49.881 sym2 -25 25
The next update statement is then applied to this to add the tot_pnl column that calculates
the running sum of the PNL across all assets.
The statement is now as shown previously, resulting in a table as below with a running sum of PNL across all assets:
q) update tot_pnl:sums r from update r:deltas pnl by sym from tablewithpnl
time sym pnl r tot_pnl
------------------------------------------
00:00:00.577 sym1 0
00:00:08.938 sym1 -8.75 0
00:00:09.049 sym1 -9 -0.25 -0.25
00:00:19.881 sym1 -22.5 -13.5 -13.75
00:00:19.881 sym1 -25.5 -3 -16.75
00:00:29.049 sym2 -16.75
00:00:39.881 sym2 50 -16.75
00:00:49.881 sym2 -25 25 8.25
Hope this helps,
Luke