CODEWITHSUNDEEP
kdb+
Chaos
Chaos

Reputation: 415

kdb/q how to join returns from dates in columns to dates in rows

I have a row of dates and symbols in rows, and values in columns for day+1(d1), day+2(d2), day+3 (d3)

date symbol d1 d2 d3
1-1-2015 a 1 1 6
1-1-2015 b 3 2 1
2-1-2015 a 1 2 1
2-1-2015 b 2 3 2
3-1-2015 a 3 2 3
3-1-2015 b 4 1 2
4-1-2015 a 4 3 3
4-1-2015 b 3 4 2

I want to return sum of values by dates so that 'd1' return of symbol 'a' is added to day 1-1-2015, 'd2' of symbol 'a' is added to 2-1-2015 (similarly for b). d1 should be added to same date and d2 to next date and d3 to the 2 dates after current date. And sum by symbols for each date. For the last two dates where data is not complete it should only sum for the number of dates available. I have only added a small portion of the table, i have the complete date for 400 dates and 300 symbols for each date.

Upvotes: 1

Views: 316

Answers (1)

Alexander Belopolsky
Alexander Belopolsky

Reputation: 2268

If I understand what you want correctly, it looks like you are looking for a query like this:

select v:sum d1+(0^prev d2)+0^prev prev d3 by date from table

Upvotes: 2

Related Questions