Showing results for 
Search instead for 
Did you mean: 

How to query a table from contents of another table?

New Contributor
Hi all

Let say I have a table with trade data and structure like [date,time,sym,price,size], and I have another table, say validtable, with a list of symbol and the valid date, like [sym,validdate]. Is there easy way to trade data with symbol in validtable, and date of individual trade are less then the validdate at validtable of that symbol?


New Contributor
Hi Carfield, 

Not sure if I'm following you entirely, perhaps you're looking for something like this.

//snippet, table is 100k rows
date       time                          sym  src price size
2017.07.18 2017.07.18D08:00:53.319000000 ORCL N   32.23 1099
2017.07.18 2017.07.18D08:01:26.649000000 YHOO O   35.52 471
2017.07.18 2017.07.18D08:01:36.413000000 YHOO L   35.52 438
2017.07.18 2017.07.18D08:01:41.763000000 YHOO N   35.51 1947

//snippet, table is 100k rows
sym validdate
IHF 2017.09.01
NAG 2017.09.03
AJD 2017.09.01
AAP 2017.09.04
NLP 2017.09.05

//Selecting from trades table where (sym is equal to sym from tab, and less than validdate from tab) - for each row in tab (which would be your validtable).
//Without (,/) you can see each individual query and what is returned from the trades table, this puts all trades together as one table.
q) (,/){select from trades where sym=x[0], date<x[1]} each flip exec (sym;validdate) from tab
date       time                          sym src price size
2017.07.18 2017.07.18D08:03:28.714000000 NOK O   31.78 3898
2017.07.18 2017.07.18D08:03:51.066000000 NOK O   31.81 573
2017.07.18 2017.07.18D08:04:11.804000000 NOK O   31.81 973
2017.07.18 2017.07.18D08:12:08.616000000 NOK L   31.76 3653

Let me know if this helps at all
Kind regards,

Thanks Matt, yes, it is, but then it need to loop through tab row by row, I just wonder if there is better way to do this....

q)meta trade
c    | t f a
-----| -----
date | d   s
sym  | s
time | t
price| f
size | j

q)meta tab
c        | t f a
---------| -----
sym      | s
validdate| d

You could use a lj:
select from (trade lj 1!tab)where date<validdate

Thanks a lot