functional update a table

Community Support
1

Hi. I’m trying to update a table named data grouping by Date, Price for the Volume at Price and save the update as the table data.

Date       Time     Price   Volume

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  6     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  6     

2016.09.15 09:30:00 2116.5  4     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.75 1     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.75 1     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  5     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  1     

2016.09.15 09:30:00 2116.5  10    

2016.09.15 09:30:00 2116.5  4     

2016.09.15 09:30:00 2116.25 11  

q)a: cpvol!((last;Price);(sum;Volume))

q)? [data;();b;a]

Date       Price  | cp      vol  

------------------| -------------

2016.09.15 2114.5 | 2114.5  532  

2016.09.15 2114.75| 2114.75 700  

2016.09.15 2115   | 2115    1256 

2016.09.15 2115.25| 2115.25 4271 

2016.09.15 2115.5 | 2115.5  6044 

2016.09.15 2115.75| 2115.75 10145

2016.09.15 2116   | 2116    12016

2016.09.15 2116.25| 2116.25 14182

2016.09.15 2116.5 | 2116.5  12780

2016.09.15 2116.75| 2116.75 11691

2016.09.15 2117   | 2117    12788

2016.09.15 2117.25| 2117.25 9553 

2016.09.15 2117.5 | 2117.5  11294

2016.09.15 2117.75| 2117.75 7655 

2016.09.15 2118   | 2118    10976

2016.09.15 2118.25| 2118.25 9288 

2016.09.15 2118.5 | 2118.5  12333

2016.09.15 2118.75| 2118.75 13178

2016.09.15 2119   | 2119    13878

2016.09.15 2119.25| 2119.25 14204

How do I update the table as ! with the same function does not work?

q)! [data;();b;a]

Date       Time     Price   Volume cp      vol  

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  6      2116.5  12780

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  6      2116.5  12780

2016.09.15 09:30:00 2116.5  4      2116.5  12780

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.75 1      2116.75 11691

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.75 1      2116.75 11691

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  5      2116.5  12780

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  1      2116.5  12780

2016.09.15 09:30:00 2116.5  10     2116.5  12780

2016.09.15 09:30:00 2116.5  4      2116.5  12780

2016.09.15 09:30:00 2116.25 11     2116.25 14182

Thank you. I appreciate the assistance and help in learning q.

2

Hi Vogel,

Could you clarify in which way you feel it is not working, or not doing what you would expect? The update is doing the equivalent of an fby:

https://code.kx.com/q/ref/fby/

i.e. the following are equivalent, although the former is preferred, more sensible and faster:

q) update vol:sum Volume by Date, Time, Price from data

q) update vol:(sum; Volume) fby ( Date; Time; Price) from data

An update expects that you want the output to have the same number of rows as your original data, but with new or updated columns. 

If you want data to change to be the same as the output of your select, then you probably want to do:

data: 0!?[data;();b;a]

Where “0!” is unkey.

Best,

George

3

Hi George,

Thank you so much. Yes, I wanted the data to change to be the same as the output of the select and was unfamiliar with 0! unkey. 

Your instructions worked perfectly and I will do some more reading as I’m new to kdb.

Cheers,

Vogel