I have a quote table and trade table, and would like to list the quotes table and join in the trades table matching on timestamps strictly less than the timestamp of the trade.
For example:
q:([]time:10:00:00 10:01:00 10:01:00 10:01:02;sym:`ibm`ibm`ibm`ibm;qty:100 200 300 400)
t:([]time:10:01:00 10:01:00 10:01:02;sym:`ibm`ibm`ibm;px:10 20 25)
aj[`time;q;t]
returns
+------------+-----+-----+----+
| time | sym | qty | px |
+------------+-----+-----+----+
| 10:00:00 | ibm | 100 | |
| 10:01:00 | ibm | 200 | 20 |
| 10:01:00 | ibm | 300 | 20 |
| 10:01:02 | ibm | 400 | 25 |
+------------+-----+-----+----+
But I'm trying to get a result like:
+------------+-----+-----+----+
| time | sym | qty | px |
+------------+-----+-----+----+
| 10:00:00 | ibm | 100 | |
| 10:01:00 | ibm | 100 | 10 |
| 10:01:00 | ibm | 100 | 20 |
| 10:01:02 | ibm | 300 | 25 |
+------------+-----+-----+----+
Is there a join function that can match based on timestamps that are strictly less than time instead up-to and including?
I think if you do some variation of aj[`time;q;t] then you won't be able to modify the qty column as table t does not contain it. Instead you may need to use the more "traditional" aj[`time;t;q]:
q)@[;`time;+;00:00:01]aj[`time;@[t;`time;-;00:00:01];q]
time sym px qty
-------------------
10:01:00 ibm 10 100
10:01:00 ibm 20 100
10:01:02 ibm 25 300
This shifts the times to avoid matching where they are equal but does not contain a row for each quote you had in the beginning.
I think if you wish to join trades to quotes rather than quotes to trades as I have done you may need to think of some method of differentiating between 2 trades that occur at the same time as in your example. One method to do this may be to use the order they arrive, i.e. match first quote to first trade.