I have a table:
q)data:([]dt:2017.01.05D19:45:00.238248239 2017.01.05D20:46:00.282382392 2017.01.05D21:47:00.232842342 2017.01.05D22:48:00.835838442 2017.01.05D20:49:00.282382392;sym:`AAPL`GOOG`AAPL`BBRY`GOOG;price:101.20 800.20 102.30 2.20 800.50;shares:500 100 500 900 100)
q)data
dt sym price shares
2017.01.05D19:45:00.238248239 AAPL 101.20 500
2017.01.05D20:46:00.282382392 GOOG 800.20 100
2017.01.05D21:47:00.232842342 AAPL 102.30 500
2017.01.05D22:48:00.835838442 BBRY 2.20 900
2017.01.05D20:49:00.282382392 GOOG 800.50 100
I need to create a column containing the sum of price*shares for the latest observation of each individual ticker.
To demonstrate using the above data, we're looking for:
data:
dt sym price shares index
2017.01.05D19:45:00.238248239 AAPL 101.20 500 50,600
2017.01.05D20:46:00.282382392 GOOG 800.20 100 130,620
2017.01.05D21:47:00.232842342 AAPL 102.30 500 131,170
2017.01.05D22:48:00.835838442 BBRY 2.20 900 133,150
2017.01.05D20:49:00.282382392 GOOG 800.50 100 133,180
To further clarify, at row 1, only 1 symbol is included, at row 2, 2 symbols, then 2 again, then 3, then 3 again at row 5.
Answered in a different thread: Apply formula to current and previous rows only (Q/KDB)
Given the question has changed considerably since the initial posting and my previous answer, here is an updated solution:
q)delete ind from update index:sum@'ind from (update ind:@\[()!();sym;:;shares*price] from data) where i>=max(first;i)fby sym
dt sym price shares index
------------------------------------------------------
2017.01.05D19:45:00.238248239 AAPL 101.2 500
2017.01.05D20:46:00.282382392 GOOG 800.2 100
2017.01.05D21:47:00.232842342 AAPL 102.3 500
2017.01.05D22:48:00.835838442 BBRY 2.2 900 133150
2017.01.05D20:49:00.282382392 GOOG 800.5 100 133180
Or without the other initial condition that it should only be populated once all tickers have ticked:
q)delete ind from update index:sum@'ind from update ind:@\[()!();sym;:;shares*price] from data
dt sym price shares index
------------------------------------------------------
2017.01.05D19:45:00.238248239 AAPL 101.2 500 50600
2017.01.05D20:46:00.282382392 GOOG 800.2 100 130620
2017.01.05D21:47:00.232842342 AAPL 102.3 500 131170
2017.01.05D22:48:00.835838442 BBRY 2.2 900 133150
2017.01.05D20:49:00.282382392 GOOG 800.5 100 133180
(Note these are only minor modifications to the solution I posted yesterday, updated for the changed requirements in the question)