Running Filled Aggregate

Community Support
1

Hi Guys, have another quick question if someone can help

I’d like to have a running sum by time but aggregating the last values by sym.

ie

``a insert enlist (symtimeval)!(A;14t;1f);a insert enlist (symtimeval)!(A;14:01t;1f);a insert enlist (symtimeval)!(B;14:02t;1f);a insert enlist (symtimeval)!(A;14:03t;1f);a insert enlist (symtimeval)!(A;14:04t;2f);a insert enlist (symtimeval)!(B;14:05t;2f);a insert enlist (symtimeval)!(A;14:06t;3f);sym time valA 14:00:00.000 1A 14:01:00.000 1B 14:02:00.000 1A 14:03:00.000 1A 14:04:00.000 2B 14:05:00.000 2A 14:06:00.000 3`

So would like to have a running sum of the last values by sym

sym time val totalA 14:00:00.000 1 1A 14:01:00.000 1 1B 14:02:00.000 1 2A 14:03:00.000 1 2A 14:04:00.000 2 3B 14:05:00.000 2 4A 14:05:00.000 3 5

I can do it by making the table global and going through each time and aggregating but that is definetly not the right way to do this.  Or I think I could do it by filling all the missing time for each sym and then aggregating that way but that also seems like overkill.  I think there must be a nice simple way that I’m missing.

Any ideas?

Thanks!

2

q)update total:sum each {@[x;y;:;z]}\[()!();sym;val] from asym time val total--------------------------A 14:00:00.000 1 1A 14:01:00.000 1 1B 14:02:00.000 1 2A 14:03:00.000 1 2A 14:04:00.000 2 3B 14:05:00.000 2 4A 14:06:00.000 3 5q)select {@[x;y;:;z]}\[()!();sym;val] from aval---------(,A)!,1f(,A)!,1fAB!1 1fAB!1 1fAB!2 1fAB!2 2fAB!3 2f

Each loop updates the last value dictionary with the latest value for that sym and returns the full last value dictionary which we then sum over to get the totals. The second example shows the last value dictionary output at each step

3

update sums total from update total:deltas val by sym from asym time val total--------------------------A 14:00:00.000 1 1A 14:01:00.000 1 1B 14:02:00.000 1 2A 14:03:00.000 1 2A 14:04:00.000 2 3B 14:05:00.000 2 4A 14:06:00.000 3 5

This should work too

4

Thanks Mark, much appreciated