'As-of' behaviour in q-sql?

[jkm]

   Is there a way to retrieve a quote valid 'as-of' a time point using q-sql? Here is my problem: we are thinking of storing market quotes in splayed partitioned tables (one table per instrument). Given an in-memory table of trades, what would be the best way to retrieve the quote information (bid/offer) from the disk? The aj join works fine (aj[`date`time;select from trades;select date, time, bid, offer from mdata_instrument]) if quotes for all instruments are stored in the same table but we will have one table per instrument. If we create an API  utility function retrieving the right quote for a given instrument, date and time, what is the best way of replicating the as-of behaviour of the aj join in this function? This function would determine which table to query based on the instrument passed to it but I am not sure how to retrieve the 'most recent' quote.

   Thanks in advance  

[James_Burrows]

Hi,

asof will still allow you to do what you're trying to do:

http://code.kx.com/q/ref/joins/#asof

for example:

    qt asof `sym`time!(`IBM;2018.03.22D09:22:46.028495000)

    bid | 105.16

    ask | 106.06

    bsize| 69

    asize| 86

    mode | "L"

    ex | "N"

This has found the most recent IBM quote in my qt table as of the time I gave it.

However, you should still be able to use aj in this manner, by providing the right-hand select statement (i.e. the select on the on-disk data) with the exact, instrument-specific quote table:

    aj[`sym`time;trades;select from ibmquotetable]

Hope this helps,

James

[jkm]

  James, thank you, the asof operator is exactly what I was looking for.