Consider the following two tables, table_a and table_b. table_a has data for an entire day, and table_b has data within certain ranges.

The question is: how can I get entries from table_a which fall within table_b's start and end times, for each row? I am trying to aggregate all the data in table_a for each start and end time found in each row of table_b (i.e. sum up all values below a certain threshold found in a column in table_b, etc.).

The data types and data for each appear as follows:

table_a
/ other columns
"time" t
"sym" s
"size" i 

table_b
/ other columns
"startTime" t
"endTime" t

For the columns with type time, they appear as "10:25:00.015", as an example.

What would the most efficient way to do this be? I'm trying to do a window join, but running into length errors:

myWindow:(select startTime from table_b; select endTime from table_b);
wj[myWindow;`sym`time;table_a;(table_b;(sum;`size);(wavg;`price;`size))]

Consider the following two tables, table_a and table_b. table_a has data for an entire day, and table_b has data within certain ranges.

The question is: how can I get entries from table_a which fall within table_b's start and end times, for each row? I am trying to aggregate all the data in table_a for each start and end time found in each row of table_b (i.e. sum up all values below a certain threshold found in a column in table_b, etc.).

The data types and data for each appear as follows:

table_a
/ other columns
"time" t
"sym" s
"size" i 

table_b
/ other columns
"startTime" t
"endTime" t

For the columns with type time, they appear as "10:25:00.015", as an example.

What would the most efficient way to do this be? I'm trying to do a window join, but running into length errors:

myWindow:(select startTime from table_b; select endTime from table_b);
wj[myWindow;`sym`time;table_a;(table_b;(sum;`size);(wavg;`price;`size))]

• kdb+

Share Improve this question Follow asked Nov 15, 2024 at 23:51 abhiabhi 1,75622 gold badges2424 silver badges4545 bronze badges 2

• please, refer to : stackoverflow/help/minimal-reproducible-example not a verbalisation , ideally with some actual sample inputs and expected outputs along with your attempts. – ticktalk Commented Nov 16, 2024 at 1:14
• Think the error might be in your window creation. You're passing in two tables rather than two lists. Try table_b`startTime`endTime ? – Darren Commented Nov 17, 2024 at 8:46

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

As mentioned the length error occurs because you pass the wrong type/format as window. It's supposed to be a pair of lists. Please review the syntax here https://code.kx/q/ref/wj/ Also, you're probably interested in wj1 rather than wj as wj includes the prevailing data point whereas wj1 only considers the data points within the time window

There are few ways to group table a by ranges from table b.
One very straightforward approach is to check if time is within each range explicitly.

N: 1000;
a: `time xasc ([] time: N?.z.t; sym: N#`XXXYYY; size: N#10);
b: ([] startTime: 1 5t; endTime: 2 6t);
a[`range]: first each where each flip ((a`time)>=/:b`startTime) & (a`time)<=/:b`endTime;
(select from a where not null range) lj `range xkey update range: i from b

Above code selects the first fitting range from table b.

If ranges do not overlap, you can aj by startTime and see if time from table a is less or equals than endTime:

N: 1000;
a: `time xasc ([] time: N?.z.t; sym: N#`XXXYYY; size: N#10);
b: `startTime xasc ([] startTime: 1 5t; endTime: 2 6t);
a: aj[`time; a; select time: startTime, startTime, endTime from b];
select from a where time <= endTime