Showing results for 
Search instead for 
Did you mean: 

Dictionary lookup

New Contributor
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

New Contributor
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
  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.

New Contributor III
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.

New Contributor
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]