Look Up the Previous Value with Asof

Question

Given the following dictionary, which contains a specific symbol and time value:

d:`sym`time!(`A;09:32:50.000)

and the following trades table:

trades:([] sym:`A`B`A`A`B;
           time:09:30:00.000 09:31:00.000 09:32:10.000 09:33:00.000 09:33:30.000;
           price:100.2 99.8 101.5 101.9 100.1;
           size:5 7 3 4 2)

Extract from the trades table the price prevailing at this time (the most recent trade at or before it) for symbol A.

Solution

asof[trades;d]

price| 101.5
size | 3

asof[trades;d][`price]    / 101.5

Explanation

asof looks up a single row in a table that's the prevailing match for a given set of key/time values — it's the row-at-a-time counterpart to aj, which performs the same kind of lookup across an entire table of search keys at once.

d supplies the lookup criteria as a dictionary: match sym exactly (`A), and find the row whose time is the most recent one at or before 09:32:50.000. asof[trades;d] returns that matching row as a dictionary of its remaining columns (price and size here); indexing it with `price pulls out just the price value, 101.5.

This is conceptually identical to the time-bucketing logic behind aj and window joins — finding "the most recent row up to this point in time" — just applied to a single lookup rather than joining an entire table of them at once.