Sorry for the bad title..
Wondering if someone can help. I have a table something like
t:time xasc (sym: 10000?(),a;time:10000?12:00:00;price:10000?100)
and I want to add a column here called passed, which would be based on 2 conditions
1 - for each row the first price after that the price is say 10% below the current row price
2 - and or the time is > 03:00t
I’m not quite sure how to iterate each row here and get these conditions, any idea?
Thanks!
I think the keyword you are looking for is ‘next’. No need for iteration :). You can use it nicely in a functional update to achieve your goals:
t:time xasc ([]sym: 10000?(),a;time:10000?12:00:00;price:10000?100);
// t = table; c = conds; n = new column name
f:{[t;c;n]
c:@[c;where 10h=type each c;parse];
![t;c;0b;enlist[n]!enlist 1b]
};
// accepts strings as if writing the conditions in free form qsql
c:(“time>3t”;“next[price]<price*0.9”);
// parse trees
c2:((>;time;3t);(\<;(next;price);(*;`price;0.9));
// single where
c3:enlist “price<50”;
select count i by passed from f[t;c;`passed]
passed| x
------| ----
0 | 6594
1 | 3406
// should be the same
select count i by passed from f[t;c2;`passed]
passed| x
------| ----
0 | 6594
1 | 3406
// different test (c3) where price<50
select count i by passed from f[t;c3;`passed]
passed| x
------| ----
0 | 4875
1 | 5125
// should be the same
select count i by price<50 from t
x| x
-| ----
0| 4875
1| 5125
HTH,
Sean
Hm not exactly but thanks, maybe this explains a bit more
t:time xasc (sym: 10000?(),a;time:10000?12:00:00;price:10000?100;cond:10000#0.5);t:update priceCond:price*(1-cond) from t
so here
sym time price cond priceCond passeda 00:00:02 59 0.5 29.5 00:01:01 a 00:00:33 98 0.5 49 00:00:58a 00:00:37 56 0.5 28 00:01:01a 00:00:58 41 0.5 20.5 ....a 00:01:00 51 0.5 25.5a 00:01:01 15 0.5 7.5a 00:01:12 15 0.5 7.5
so here on the first row am looking for the time of the row that passes prce<priceCond so for the first row it would be the second row 00:01:01
on the second row looking for the next row that the price < 49, which would be 00:00:58
and then same for the third row, looking for time of next price <28 which would be 00:01:01
does that make sense?
The key here is, given a vector, finding an efficient way to find the index of the first element of that vector less than some other given value.
Given a table similar to yours above (NB:my price column is of float type, not long):
q)system"S 10223" // fix seed for reproducibilityq)show t:time xasc (sym:a;time:10000?12:00:00;price:10000?1000f;cond:0.5)sym time price cond--------------------------a 00:00:01 320.1243 0.5a 00:00:02 887.3237 0.5a 00:00:03 927.6216 0.5a 00:00:04 726.379 0.5a 00:00:12 936.3665 0.5a 00:00:17 218.2577 0.5a 00:00:18 271.1841 0.5a 00:00:19 508.1405 0.5a 00:00:20 121.5772 0.5a 00:00:22 820.7361 0.5..q)show t:update priceCond:price*1-cond from tsym time price cond priceCond------------------------------------a 00:00:01 320.1243 0.5 160.0621a 00:00:02 887.3237 0.5 443.6619a 00:00:03 927.6216 0.5 463.8108a 00:00:04 726.379 0.5 363.1895a 00:00:12 936.3665 0.5 468.1832a 00:00:17 218.2577 0.5 109.1289a 00:00:18 271.1841 0.5 135.592a 00:00:19 508.1405 0.5 254.0702a 00:00:20 121.5772 0.5 60.78861a 00:00:22 820.7361 0.5 410.3681
we can create a dictionary which does exactly what we’d like:
q)show d:exec s#reverse first each group mins price from t0.0587129| 25620.2813467| 8160.5073543| 4762.147674 | 4738.340432 | 3389.530393 | 2419.990613 | 13337.04542 | 10638.33161 | 7654.87185 | 1975.80056 | 10121.5772 | 8218.2577 | 5320.1243 | 0q)d 50f76q)d 200f8q)d 10f133`
So, for example, row 133 is the first row with price less than 10.0
q)t 133sym | atime | 00:08:53price | 9.990613cond | 0.5priceCond| 4.995307`
We can index into the time column in our table to generated the ‘passed’ column you want
q)update passed:time d price from tsym time price cond priceCond passed---------------------------------------------a 00:00:01 320.1243 0.5 160.0621 00:00:01a 00:00:02 887.3237 0.5 443.6619 00:00:01a 00:00:03 927.6216 0.5 463.8108 00:00:01a 00:00:04 726.379 0.5 363.1895 00:00:01a 00:00:12 936.3665 0.5 468.1832 00:00:01a 00:00:17 218.2577 0.5 109.1289 00:00:17a 00:00:18 271.1841 0.5 135.592 00:00:17a 00:00:19 508.1405 0.5 254.0702 00:00:01a 00:00:20 121.5772 0.5 60.78861 00:00:20a 00:00:22 820.7361 0.5 410.3681 00:00:01a 00:00:24 75.80056 0.5 37.90028 00:00:24a 00:00:26 725.6273 0.5 362.8136 00:00:01a 00:00:26 295.0753 0.5 147.5376 00:00:17..
Cheers,
Jorge