kdb - how to augment table with missing dates in a dynamic/fast way

Question

I have an in-memory table with (date, sym, symType, factor, weight) as columns.

There are cases where this in-memory table once queried for a particular date range is missing an entire date. Could be today's data, or if we're querying for multiple dates, could be a day in the middle, or perhaps multiple days, or the last date, or the beginning.

How can I come up with with a query that fills in those missing dates with the max date up to that point?

So if we have data as follows: Examples:

.z.D 
.z.D-2
.z.D-3
.z.D-6
.z.D-7

I'd like the table to look like this:

.z.D -> .z.D
.z.D-1 -> copy of .z.D-2
.z.D-2 -> .z.D-2
.z.D-3 -> .z.D-3
.z.D-4 -> copy of .z.D-6
.z.D-5 -> copy .z.d-6
.z.D-6 -> .z.D-6
.z.D-7 -> .z.D-7

If in your query today is missing, use previous available date as today. If in your query the last day is yesterday and it's missing, use the the previous available day as yesterday and so on. if your last (min date) is missing, use the next available date upwards.

I can do this manually by identifying missing dates and going through missing dates day by day, but I'm wondering if there's a much better way to do this.

aj can work for dates in the middle by constructing a ([] date: listofdesireddates) cross ([] sym: listofsyms) cross ([] sectors: symtype) and then do an aj with the table but it doesn't solve all cases e.g if the missing day is today or at the start.

terrylynch · Accepted Answer · 2020-07-20 08:54:14Z

1

Can you come up with a reproducible example as to why aj doesn't work? Normal aj usage should solve this problem:

t1:([]date:.z.D-til 8;sym:`ABC);
t2:`date xasc([]date:.z.D-0 2 3 6 7;sym:`ABC;data:"I"$ssr[;".";""]each string .z.D-0 2 3 6 7);

q)aj[`sym`date;t1;t2]
date       sym data
-----------------------
2020.07.20 ABC 20200720
2020.07.19 ABC 20200718
2020.07.18 ABC 20200718
2020.07.17 ABC 20200717
2020.07.16 ABC 20200714
2020.07.15 ABC 20200714
2020.07.14 ABC 20200714
2020.07.13 ABC 20200713

/If you need your last date to fill "upwards" then use fills:

update fills data by sym from aj[`sym`date;([]date:.z.D-til 9;sym:`ABC);t2]

answered Jul 20, 2020 at 8:54

terrylynch

14k1 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kkudi Over a year ago

looks like this is working as expected - not sure why I thought it didnt. Thank you @terrylynch

Darren · Accepted Answer · 2020-07-17 22:49:42Z

0

A quick guess but a step function with xgroup on the result seems like it will work.

res:getFromTab[dates];
f:{`date xcols:update date:x from y@x};
xgrp:`s#`date xasc `date xgroup res;
raze f[;xgrp] each dates

Performance might be horrible ...

answered Jul 17, 2020 at 22:49

Darren

5592 silver badges10 bronze badges

Collectives™ on Stack Overflow

kdb - how to augment table with missing dates in a dynamic/fast way

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related