calculate lead and lag in Kx

Community Support
1

Hi, I have a table with follwoing sample data: date sym price2009.09.08 AAPL 172.932009.09.09 AAPL 171.142009.09.10 AAPL 172.562009.09.11 AAPL 172.162009.09.14 AAPL 173.722009.09.15 AAPL 175.16 I would like to have “lag” data (as using lag function in Oracle)for 1 and 3 days, with the results as in the following: date sym price price_prevprice_prev_3day2009.09.08 AAPL 172.932009.09.09 AAPL 171.14 172.932009.09.10 AAPL 172.56 171.142009.09.11 AAPL 172.16 172.56 172.932009.09.14 AAPL 173.72 172.16 171.142009.09.15 AAPL 175.16 173.72 172.56 Is there an easy way to do it? Thanks. NingIs ther

2

q)t:( date:2009.09.08+til 6;sym:6#`AAPL;price:172.93 171.14 172.56
172.16 173.72 175.16)

q)update prevPrice:prev price,prevPrice3D:3 xprev price from t
date sym price prevPrice prevPrice3D

2009.09.08 AAPL 172.93
2009.09.09 AAPL 171.14 172.93
2009.09.10 AAPL 172.56 171.14
2009.09.11 AAPL 172.16 172.56 172.93
2009.09.12 AAPL 173.72 172.16 171.14
2009.09.13 AAPL 175.16 173.72 172.56

3

To: personal-kdbplus@googlegroups.comX-Mailer: Apple Mail (2.1076)update price_prev:prev price,price_prev_3day:2 xprev price from tableRegards, AttilaOn 29 Sep 2009, at 19:54, Ninja Li wrote:>> Hi,>> I have a table with follwoing sample data:>> date sym price> 2009.09.08 AAPL 172.93> 2009.09.09 AAPL 171.14> 2009.09.10 AAPL 172.56> 2009.09.11 AAPL 172.16> 2009.09.14 AAPL 173.72> 2009.09.15 AAPL 175.16>> I would like to have “lag” data (as using lag function in Oracle)> for 1 and 3 days, with the results as in the following:>> date sym price price_prev> price_prev_3day> 2009.09.08 AAPL 172.93> 2009.09.09 AAPL 171.14 172.93> 2009.09.10 AAPL 172.56 171.14> 2009.09.11 AAPL 172.16 172.56 172.93> 2009.09.14 AAPL 173.72 172.16 171.14> 2009.09.15 AAPL 175.16 173.72 172.56>> Is there an easy way to do it?>> Thanks.>> Ning>> Is ther> >

4

Thanks all for your help. It works well.On Sep 29, 3:42?pm, Attila Vrabecz <attila.vrab…> wrote:> update price_prev:prev price,price_prev_3day:2 xprev price from table>> Regards,> ? ?Attila> On 29 Sep 2009, at 19:54, Ninja Li wrote:>>>>>> > Hi,>> > ? I have a table with follwoing sample data:>> > ? ?date ? ? ? ? ? sym ? ? ? ?price> > 2009.09.08 ? ?AAPL ? 172.93> > 2009.09.09 ? ?AAPL ? 171.14> > 2009.09.10 ? ?AAPL ? 172.56> > 2009.09.11 ? ?AAPL ? 172.16> > 2009.09.14 ? ?AAPL ? 173.72> > 2009.09.15 ? ?AAPL ? 175.16>> > ? I would like to have “lag” ?data (as using lag function in Oracle)> > for 1 and 3 days, with the results as in the following:>> > ?date ? ? ? ? ? ? sym ? ? ? price ? ? ?price_prev> > price_prev_3day> > 2009.09.08 ? ?AAPL ? 172.93> > 2009.09.09 ? ?AAPL ? 171.14 ? ? 172.93> > 2009.09.10 ? ?AAPL ? 172.56 ? ? 171.14> > 2009.09.11 ? ?AAPL ? 172.16 ? ? 172.56 ? ? ? ? ? ?172.93> > 2009.09.14 ? ?AAPL ? 173.72 ? ? 172.16 ? ? ? ? ? ?171.14> > 2009.09.15 ? ?AAPL ? 175.16 ? ? 173.72 ? ? ? ? ? ?172.56>> > ?Is there an easy way to do it?>> > ?Thanks.>> > ?Ning>> > Is ther- Hide quoted text ->> - Show quoted text -</attila.vrab…>

5

X-Mailer: Apple Mail (2.936)If you need to generalize it, you can parameterize the column names and the day counts with a functional query:![table;();0b;{($"price_prev_",/:string[x],\:"day")! (xprev),'x,'price}1 3]e.g.q)![table;();0b;{($"price_prev_",/:string[x],\:"day")! (xprev),'x,'price}1 3 5 7]date sym price price_prev_1day price_prev_3day price_prev_5day price_prev_7day--------------------------------------------------------------------------------------2009.09.08 AAPL 172.932009.09.09 AAPL 171.14 172.932009.09.10 AAPL 172.56 171.142009.09.11 AAPL 172.16 172.56 172.932009.09.14 AAPL 173.72 172.16 171.142009.09.15 AAPL 175.16 173.72 172.56 172.93> Thanks all for your help. It works well.>>> update price_prev:prev price,price_prev_3day:2 xprev price from table