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