Is there a specific reason why the following works with a single item
select [-1] sym,bar,open,high,low,close,volume,
dx:forex_bar[enlist (bar-00:05:00),sym]close
from forex_bar where sym=GBPUSD
But with a multi item lookup it fails
select [-10] sym,bar,open,high,low,close,volume, dx:forex_bar[enlist (bar-00:05:00),sym]close from forex_bar where sym=GBPUSD
I assume, sym and bar are the key columns in your table. To make multiple lookup work, you need to specify dx as dx:forex_bar[(sym;bar-00:05:00);`close]
Many thanks, have spent weeks looking for the optimal solution and this solved it perfectly.
As a follow up, would you have a suggestion for the following.
select bar,sym prevclose:forex_bar(sym;i-50);close from forex_bar where bar within 2017.08.18T10:00:00 2017.08.17T11:00:00,sym=GBPUSD
Need to efficiently find the previous close from 50 bars ago, the problem is that i is not sequential per sym but on the entire table. xprev was a suggestion but it needs to read the entire dataset and was slow, or would having a pseudo id be more preferable in the update routine. Part of the issue is that there are data gaps so a direct lookup often fails to return the last result. What is actually required is first close > i-50 by sym.
If all your operations are around working with specific symbols, why not build a dict of tables (key of the dict are the symbols, value of the dict are the data related to that sym)? With such structure, you can easily access whichever symbol’s data and perform all the necessary calculations on a much smaller data set.
Thanks for the reply, on a functional basis the data is inconsistent and need to pull a specific bar as cannot guarantee a time based bar exists, the following does work even if not ideal.
prevclose:raze {[x;y;z] -1#?[forex_bar;((<=;bar;x);(=;sym;enlist y));0b;enlist [close]!enlist (z xprev;close);-500]close} '[bar;sym;dz]