0

I have data in a table with the following schema: date, time, sym, book, pnl

This is a timeseries. sym/book as columns define the timeseries.

I have a special usecase where I need to come up with another timeseries that combines two books together.

If this wasn't a timeseries, this would be fairly easy, just sum by book/sym, filter on the books I Want to combine, and sum again with the the new book name (constant)

But I'm not sure how to create a timeseries with one book value (which is the combination of two at any given in time e.g the distinct times of the combination of both books). It's important to say that the timeseries isn't even/uniform and that the times are "random" for a bookId/sym combination.

t: ([] date: 4#.z.D; time: (07:00; 07:00; 07:01; 07:02); sym: `x`x`x`x; book: `book1`book2`book2`book1; v: (100; 0; 200; 200))

c: ([] date: 3#.z.D; time: (07:00; 07:01; 07:02); sym: `x`x`x; book: `newbook; v: (100; 300; 400))
kkudi
  • 1,625
  • 4
  • 25
  • 47

1 Answers1

2

Assuming from your expected output that you want to know the total holdings across multiple books at any given time, I think this should fit your purpose.

q)select date,time,sym,book:`newbook,v:sum each vb from update vb:@[;;:;]\[()!();book;v]from t
date       time  sym book    v
--------------------------------
2020.12.22 07:00 x   newbook 100
2020.12.22 07:00 x   newbook 100
2020.12.22 07:01 x   newbook 300
2020.12.22 07:02 x   newbook 400

This solution is using a scan (\) to create a dictionary of most recent value for each book, and then summing them. A distinct may need to be added in case there are any rows where nothing has changed.

Cathal O'Neill
  • 2,522
  • 1
  • 6
  • 17
  • thank you, I tested this with my data - and it doesnt work. I think the problem is the fact that times do not align between each timeseries. .I've been able to find a solution with two ajs, but it's not very efficient. – kkudi Dec 22 '20 at 19:04
  • Is an error being thrown when you try this solution or is it not providing the right output? Perhaps I have misunderstood your question. – Cathal O'Neill Dec 22 '20 at 19:27
  • Would you be able to share your aj solution? That might give me a better idea of what you’re trying to do. – Cathal O'Neill Dec 23 '20 at 15:05