Whats the best way to iterate and select based on multiple column

[hoffmanroni]

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!

[sohagan857]

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

[hoffmanroni]

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   passed

a 00:00:02 59 0.5 29.5      00:01:01  

a 00:00:33 98 0.5 49        00:00:58

a 00:00:37 56 0.5 28        00:01:01

a 00:00:58 41 0.5 20.5      ....

a 00:01:00 51 0.5 25.5

a 00:01:01 15 0.5 7.5

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

[jsawyer]

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 reproducibility

q)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.5

a   00:00:02 887.3237 0.5

a   00:00:03 927.6216 0.5

a   00:00:04 726.379  0.5

a   00:00:12 936.3665 0.5

a   00:00:17 218.2577 0.5

a   00:00:18 271.1841 0.5

a   00:00:19 508.1405 0.5

a   00:00:20 121.5772 0.5

a   00:00:22 820.7361 0.5

..

q)show t:update priceCond:price*1-cond from t

sym time     price    cond priceCond

------------------------------------

a   00:00:01 320.1243 0.5  160.0621

a   00:00:02 887.3237 0.5  443.6619

a   00:00:03 927.6216 0.5  463.8108

a   00:00:04 726.379  0.5  363.1895

a   00:00:12 936.3665 0.5  468.1832

a   00:00:17 218.2577 0.5  109.1289

a   00:00:18 271.1841 0.5  135.592

a   00:00:19 508.1405 0.5  254.0702

a   00:00:20 121.5772 0.5  60.78861

a   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 t

0.0587129| 2562

0.2813467| 816

0.5073543| 476

2.147674 | 473

8.340432 | 338

9.530393 | 241

9.990613 | 133

37.04542 | 106

38.33161 | 76

54.87185 | 19

75.80056 | 10

121.5772 | 8

218.2577 | 5

320.1243 | 0

q)d 50f

76

q)d 200f

8

q)d 10f

133

So, for example, row 133 is the first row with price less than 10.0

q)t 133
sym      | `a
time     | 00:08:53
price    | 9.990613
cond     | 0.5
priceCond| 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 t

sym time     price    cond priceCond passed

---------------------------------------------

a   00:00:01 320.1243 0.5  160.0621  00:00:01

a   00:00:02 887.3237 0.5  443.6619  00:00:01

a   00:00:03 927.6216 0.5  463.8108  00:00:01

a   00:00:04 726.379  0.5  363.1895  00:00:01

a   00:00:12 936.3665 0.5  468.1832  00:00:01

a   00:00:17 218.2577 0.5  109.1289  00:00:17

a   00:00:18 271.1841 0.5  135.592   00:00:17

a   00:00:19 508.1405 0.5  254.0702  00:00:01

a   00:00:20 121.5772 0.5  60.78861  00:00:20

a   00:00:22 820.7361 0.5  410.3681  00:00:01

a   00:00:24 75.80056 0.5  37.90028  00:00:24

a   00:00:26 725.6273 0.5  362.8136  00:00:01

a   00:00:26 295.0753 0.5  147.5376  00:00:17

..

Cheers,

Jorge