How to query a table from contents of another table?

[carfield1]

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,

[AquaQMatt]

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

[carfield1]

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

[Jamie_O__Mahony]

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

[carfield1]

Thanks a lot