Create unique calendar entries (Q / KDB +)

Question

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 the * stock price for the last observation of each individual ticker.

To demonstrate the use of the above data, we look 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 clarify, in line 1 only 1 character is included, in line 2, 2 characters, then 2 again, then 3, then 3 again in line 5.

Answered in another thread: Apply formula to current and previous lines only (Q / KDB)

Answer 1

Given that the question has changed significantly since the initial publication 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 another initial condition, that it should be filled only after all tickers are checked:

 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 

(Please note that these are just minor changes to the solution I posted yesterday, updated for the changed requirements in the question)

Answer 2

Minor change on Jonathon's solution using a conditional vector:

 q)delete dict from update index:?[all flip distinct[sym]in/: key'[dict]; {sum[x]*sum[y]%sum z} ./: flip each value each dict;0N] from update dict:@[;;:;]\[()!();sym;flip (price;shares;divisor)] from data dt sym price shares divisor index ---------------------------------------------------------------- 2018.02.05D22:47:22.175914000 AAPL 101.2 500 2 2018.02.05D22:21:10.175914000 GOOG 800.2 500 1 2018.02.05D22:58:00.175914000 AAPL 102.3 500 2 2018.02.05D22:19:27.175914000 BBRY 2.2 500 1 339262.5 

Answer 3

 q)delete ind from update index: sum@ 'ind from (update ind:@\[()!();sym;:;shares*price%divisor] from data) where i>=max(first;i)fby sym dt sym price shares divisor index -------------------------------------------------------------- 2017.01.05D19:45:00.238248239 AAPL 101.2 500 2 2017.01.05D20:45:00.282382392 GOOG 800.2 500 1 2017.01.05D21:45:00.232842342 AAPL 102.3 500 2 2017.01.05D22:45:00.835838442 BBRY 2.2 500 1 426775 

A slightly different answer to what you got is that it makes a sum (share * price% divisor), rather than summing each separately.

A slightly more dirty and complicated version that gets the same answer as you would expect:

 q)delete ind from update index:sum'[ind[;;0]]*sum'[ind[;;1]]%sum'[ind[;;2]] from (update ind:@\[()!();sym;:;shares,'price,'divisor] from data) where i>=max(first;i)fby sym dt sym price shares divisor index ---------------------------------------------------------------- 2017.01.05D19:45:00.238248239 AAPL 101.2 500 2 2017.01.05D20:45:00.282382392 GOOG 800.2 500 1 2017.01.05D21:45:00.232842342 AAPL 102.3 500 2 2017.01.05D22:45:00.835838442 BBRY 2.2 500 1 339262.5