KDB Converting Subselect to Q Query

Community Support
1

We have a Q query running on tick data which consolidates to OHLC on 1minute bars.

select subsel:(exec last datetime.date+1 xbar datetime.minute.z.Z from base where instrument=GBPUSD, datetime=datetime.date+1 xbar datetime.minute.z.Z), max(datetime),min(datetime),Open:first price,High:max price,Low:min price,Close:last price,Volume:count(i) by DT:($)datetime.date+1 xbar datetime.minute.z.Z from base where instrument=GBPUSD, datetime>=2017.07.03T10:20:00.00,datetime<2017.07.03T10:20:59.999

The problem is the xbar date is synthetic on both the main table and the ‘subselect’, the exec “datetime=” needs to reference the main table and cannot find the alias approach to use. Considered an ej but as both sides are synthetic also could not find the construct.

2

Don’t quite understand what you want to perform in subsel. Whatever you get from subsel is already computed in the main query. What’s the exact purpose of subsel?

(BTW, what is datetime.minute.z.Z? I don’t think that’s valid syntax…)

3

A previous price for a calculation, say we want the close from 10minutes ago minus this bars close.

The .z.z is just test code, trying to get the 1 xbar in to ISO date format, it always puts trailing 0s on the milliseconds.

4

Try this?

update preclose:prev close from select max(datetime),min(datetime),Open:first price,High:max price,Low:min price,Close:last price,Volume:count(i) by DT:datetime.date+1 xbar datetime.minute from base where instrument=GBPUSD

5

Many thanks for that, it gets the previous bar but how about from 10 bars ago, and what if you need multiple columns each pulling a different history.

update preclose:prev Close from select max(datetime),min(datetime),Open:first price,High:max price,Low:min price,Close:last price,Volume:count(i) by DT:datetime.date+1 xbar datetime.minute from base where instrument=GBPUSD where DT>=2017.07.05T09:30:00.000

Cannot access the alias DT above, must be missing something because it is converting a simple subselect.

SELECT Close, (SELECT close FROM base be WHERE be.datetime=(base.datetime-00:05:00) RecentClose, //09:55:00 Close(SELECT close FROM base be WHERE be.datetime=(base.datetime-00:10:00) MiddleClose, //09:50:00 Close(SELECT close FROM base be WHERE be.datetime=(base.datetime-00:15:00) OldestClose, //09:45:00 CloseFROM base WHERE datetime='2017-07-05 10:00:00.000'

6

Just replace prev x with k xprev x, which givens you the k-th previous value.

If you need multiple columns of different histories, change the last update statement and add those columns accordingly.

7

Thank you.

8

Have a follow up question, the query is working perfectly, however need only the last record from the update but if you restrict the select obviously the xprev is missing, is the most efficient to just wrap a “select [-1] from” over the update.

9

-1# …

10

Perfect, many thanks.