I am trying to use the fby function to create a new column that cumulatively sums a Volume column by a Symbol.
I have a table named x that looks like this:
| Timestamp | Symbol | VOL | Price |
|---|---|---|---|
| Cell 1 | A | 2 | 10 |
| Cell 2 | A | 5 | 20 |
| Cell 3 | A | 2 | 10 |
| Cell 4 | B | 3 | 20 |
| Cell 5 | B | 6 | 10 |
| Cell 6 | B | 1 | 20 |
I tried the following fby function to do so, but it just spits out the same as the "VOL" column:
x: update cumSum: sums (deltas;VOL) fby Symbol from x
x
| Timestamp | Symbol | VOL | Price | cumSumVOL |
|---|---|---|---|---|
| Cell 1 | A | 2 | 10 | 2 |
| Cell 2 | A | 5 | 20 | 5 |
| Cell 3 | A | 2 | 10 | 2 |
| Cell 4 | B | 3 | 20 | 3 |
| Cell 5 | B | 6 | 10 | 6 |
| Cell 6 | B | 1 | 20 | 1 |
However, this is what I am expecting the following:
| Timestamp | Symbol | VOL | Price | cumSumVOL |
|---|---|---|---|---|
| Cell 1 | A | 2 | 10 | 2 |
| Cell 2 | A | 5 | 20 | 7 |
| Cell 3 | A | 2 | 10 | 9 |
| Cell 4 | B | 3 | 20 | 3 |
| Cell 5 | B | 6 | 10 | 9 |
| Cell 6 | B | 1 | 20 | 10 |
Please let me know if you know where I'm making a mistake or have some advice, appreciate the help.
deltas isn't needed in your above example - below will accomplish what you're looking to do
q)show x:([]Timestamp:"Cell ",/:"123456";Symbol:`A`A`A`B`B`B;VOL:2 5 2 3 6 1;Price:6#10 20)
Timestamp Symbol VOL Price
--------------------------
"Cell 1" A 2 10
"Cell 2" A 5 20
"Cell 3" A 2 10
"Cell 4" B 3 20
"Cell 5" B 6 10
"Cell 6" B 1 20
q)update cumSumVOL:(sums;VOL)fby Symbol from x
Timestamp Symbol VOL Price cumSumVOL
------------------------------------
"Cell 1" A 2 10 2
"Cell 2" A 5 20 7
"Cell 3" A 2 10 9
"Cell 4" B 3 20 3
"Cell 5" B 6 10 9
"Cell 6" B 1 20 10