I have a table that has incremental data for a symbol, and what I want to do is aggregate the data and create a summary table, where it only shows the symbol, earliest time of data available, and the sum of the data.
However, I am hitting a roadblock when trying to group by symbol, and I'm not sure how I can get the earliest time of data per symbol, and I appreciate any suggestions you may have.
My data looks like this:
| DateTime | Symbol | Value |
|---|---|---|
| 1/1/20 2am | A | 2 |
| 1/1/20 12pm | A | 5 |
| 1/1/20 3pm | A | 1 |
| 1/1/20 9pm | A | 4 |
| 1/1/20 2am | B | 1 |
| 1/1/20 12pm | B | 3 |
| 1/1/20 3pm | B | 6 |
| 1/1/20 9pm | B | 9 |
I am trying to see how I can make a table that looks something like this:
| DateTime | Symbol | Value |
|---|---|---|
| 1/1/20 2am | A | 12 |
| 1/1/20 2am | B | 19 |
Here is what I have currently:
update Sum: sums (sums;Value) fby Symbol from TABLE
But this just copies the table and adds a column called Sum to the right of it, summing the previous amounts by Symbol. Note that this also doesn't try to get the first DateTime value per symbol.
I also tried this:
update (NEW: sum Value by Symbol) by DateTime from TABLE
My thinking was that this might get the sum of all Values by Symbol and then I can just keep the earliest DateTime per Symbol, creating a table called NEW in the process, but this doesn't work at all. When I run this it gives me an error, saying the 'by' keyword is somehow not right:
[0] update (NEW: sum Value by Symbol) by DateTime from TABLE
^
I appreciate any help from the kdb/q community. I wish I was experienced enough to understand the error messages. I feel like fby is helpful, but that may just be to add new columns to the table in question, so not sure if there's a different function that could be helpful for my case.
Similarly to your question yesterday, by can achieve what you want in one statement:
select min DateTime, sum Value by Symbol from TABLE