cancel
Showing results for 
Search instead for 
Did you mean: 

How to query a table from contents of another table?

carfield1
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?

Thanks,
4 REPLIES 4

AquaQMatt
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
q)trades
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
q)tab
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,
Matt






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