I have a table t defined ast:(sym:ABBBC;time:09:30:00 09:30:00 09:31:00 09:32:0009:32:00;price:1 2 2 2 10)sym time price------------------A 09:30:00 1B 09:30:00 2B 09:31:00 2B 09:32:00 2C 09:32:00 10I want to get this resultsetsym time price------------------A 09:30:00 1A 09:31:00 1A 09:32:00 1B 09:30:00 2B 09:31:00 2B 09:32:00 2C 09:30:00 10C 09:31:00 10C 09:32:00 10from the table.I came up with this, but it does not fill the first two C quotes at9:30 and 9:31. I'm thinking there must be an alternative way to dothis using asof or aj?a:(exec distinct sym from t) cross (exec distinct time from t)b:flip symtime!flip au:b lj sym`time xkey tupdate price:fills price by sym from u
This is a little unusual, in that you want to propagate startingvalues backwards. Without this requirement, your result is correct.Perhaps the first step is to fill in the initial value:q)t:(sym:ABBBC;time:09:30:00 09:30:00 09:31:00 09:32:0009:32:00;price:1 2 2 2 10)q)m:update (time:exec first time from t) from select first price bysym from tq)t:(0!m),tt now has duplicate records - use keyed upsert to avoid them.The aj now does what you want:q)a:(exec distinct sym from t) cross (exec distinct time from t)q)b:flip symtime!flip aq)aj[symtime;b;t]sym time price------------------A 09:30:00 1A 09:31:00 1A 09:32:00 1B 09:30:00 2B 09:31:00 2B 09:32:00 2C 09:30:00 10C 09:31:00 10C 09:32:00 10On Mar 31, 2:43?am, Dal Mon <dalae...> wrote:> I have a table t defined as>> t:([]sym:ABBBC;time:09:30:00 09:30:00 09:31:00 09:32:00> 09:32:00;price:1 2 2 2 10)> sym time ? ? price> ------------------> A ? 09:30:00 1> B ? 09:30:00 2> B ? 09:31:00 2> B ? 09:32:00 2> C ? 09:32:00 10>> I want to get this resultset>> sym time ? ? price> ------------------> A ? 09:30:00 1> A ? 09:31:00 1> A ? 09:32:00 1> B ? 09:30:00 2> B ? 09:31:00 2> B ? 09:32:00 2> C ? 09:30:00 10> C ? 09:31:00 10> C ? 09:32:00 10>> from the table.>> I came up with this, but it does not fill the first two C quotes at> 9:30 and 9:31. ?I’m thinking there must be an alternative way to do> this using asof or aj?>> a:(exec distinct sym from t) cross (exec distinct time from t)> b:flip symtime!flip a> u:b lj symtime xkey t> update price:fills price by sym from u</dalae…>
typoreplace:t:(0!m),tby:t:(0!m) upsert tOn Mar 31, 9:53?am, Chris Burke <cdbu…> wrote:> This is a little unusual, in that you want to propagate starting> values backwards. Without this requirement, your result is correct.>> Perhaps the first step is to fill in the initial value:>> q)t:(sym:ABBBC;time:09:30:00 09:30:00 09:31:00 09:32:00> 09:32:00;price:1 2 2 2 10)>> q)m:update (time:exec first time from t) from select first price by> sym from t> q)t:(0!m),t>> t now has duplicate records - use keyed upsert to avoid them.>> The aj now does what you want:>> q)a:(exec distinct sym from t) cross (exec distinct time from t)> q)b:flip symtime!flip a> q)aj[symtime;b;t]> sym time ? ? price> ------------------> A ? 09:30:00 1> A ? 09:31:00 1> A ? 09:32:00 1> B ? 09:30:00 2> B ? 09:31:00 2> B ? 09:32:00 2> C ? 09:30:00 10> C ? 09:31:00 10> C ? 09:32:00 10>> On Mar 31, 2:43?am, Dal Mon <dalae...> wrote:>> > I have a table t defined as>> > t:([]sym:ABBBC;time:09:30:00 09:30:00 09:31:00 09:32:00> > 09:32:00;price:1 2 2 2 10)> > sym time ? ? price> > ------------------> > A ? 09:30:00 1> > B ? 09:30:00 2> > B ? 09:31:00 2> > B ? 09:32:00 2> > C ? 09:32:00 10>> > I want to get this resultset>> > sym time ? ? price> > ------------------> > A ? 09:30:00 1> > A ? 09:31:00 1> > A ? 09:32:00 1> > B ? 09:30:00 2> > B ? 09:31:00 2> > B ? 09:32:00 2> > C ? 09:30:00 10> > C ? 09:31:00 10> > C ? 09:32:00 10>> > from the table.>> > I came up with this, but it does not fill the first two C quotes at> > 9:30 and 9:31. ?I’m thinking there must be an alternative way to do> > this using asof or aj?>> > a:(exec distinct sym from t) cross (exec distinct time from t)> > b:flip symtime!flip a> > u:b lj symtime xkey t> > update price:fills price by sym from u</dalae…></cdbu…>