how to generate multiple columns

James

unread,

Oct 22, 2020, 1:57:20 PM10/22/20

to Kdb+ Personal Developers

Hi Masters,

I have a table T like this:

date, close

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

I would like to get this one:

date, close, yield1, yield2, yield3, ... ,yield20

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

As you see, I need to get 1-20 days yield columns.

How to do this in a neat q-style. NOT like this one:

update yield1:(close%(1 xprev close))-1,yield2:(close%(2 xprev close))-1,...,yield20:(close%(20 xprev close))-1 from T

Thank you.

James

matt....@aquaq.co.uk

unread,

Oct 22, 2020, 2:28:25 PM10/22/20

to Kdb+ Personal Developers

Hi James,

You can use parse to help you workout functional form. You can then use functional form to dynamically create columns like so:

q)parse"update yield1:(close%(1 xprev close))-1,yield2:(close%(2 xprev close))-1 from t"

!

`t

()

0b

`yield1`yield2!((-;(%;`close;(k){$[0h>@y;'`rank;y(!#y)-x]};1;`close));1);(-;(..

You just need to replace the k) definitions with the q keyword when necessary.

// functional update
// ![table;where;by;columns]

![T;();0b;(`$"yield",/:string 1 + til 20)!{(-;(%;`close;(xprev;x;`close));1)} each 1 + til 20]

https://code.kx.com/q/basics/funsql/

Hope this helps,

Matt M

James

unread,

Oct 22, 2020, 2:51:05 PM10/22/20

to Kdb+ Personal Developers

Hello, Matt

This is awesome! I will have a try.

Thank you so much and have a good day!

James

Alvi Kabir

unread,

Oct 22, 2020, 3:02:27 PM10/22/20

to personal...@googlegroups.com

For table construction:

q)flip (`date`close!("d"$();"f"$())), "f"$(`$"yield" ,/: string 1+til 20)!()
date close yield1 yield2 yield3 yield4 yield5 yield6 yield7 yield8 yield9 yie..
-----------------------------------------------------------------------------..
q)meta flip (`date`close!("d"$();"f"$())), "f"$(`$"yield" ,/: string 1+til 20)!()
c | t f a
-------| -----
date | d
close | f
yield1 | f
yield2 | f
yield3 | f
yield4 | f
yield5 | f
yield6 | f
yield7 | f
yield8 | f
yield9 | f
yield10| f
yield11| f
yield12| f
yield13| f
yield14| f
yield15| f
yield16| f
yield17| f
yield18| f
..

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/personal-kdbplus/ec2eca90-166c-41d1-9293-3963b49ad1bfn%40googlegroups.com.

--

Sincerely,

Alvi Kabir

Jason Fealy

unread,

Oct 22, 2020, 3:58:19 PM10/22/20

to Kdb+ Personal Developers

Another approach

q)t:([]close:6?10.);days:1+til 5

q)@[t;`$"yield",/:string days;:;(-1+t[`close]%xprev[;t`close]@)'[days]]

close yield1 yield2 yield3 yield4 yield5

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

3.927524

5.170911 0.316583

5.159796 -0.002149466 0.3137531

4.066642 -0.21186 -0.2135541 0.03542133

1.780839 -0.5620862 -0.6548626 -0.6556045 -0.5465747

3.017723 0.6945514 -0.2579326 -0.415147 -0.4164041 -0.2316476

James

unread,

Oct 22, 2020, 5:00:33 PM10/22/20

to Kdb+ Personal Developers

Thank you Jason.

Alvi Kabir

unread,

Oct 25, 2020, 10:26:04 AM10/25/20

to Kdb+ Personal Developers

Another approach: Build up column dictionary and pass it into functional update:

q)show t:([]date:.z.d-til 100;close:100?100f)

date close

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

2020.10.23 54.97936

2020.10.22 19.58467

2020.10.21 56.15261

2020.10.20 7.043811

2020.10.19 21.24007

2020.10.18 77.7882

2020.10.17 48.44727

2020.10.16 68.27999

2020.10.15 15.3227

2020.10.14 53.50923

2020.10.13 45.7328

2020.10.12 0.8062521

2020.10.11 10.39343

2020.10.10 10.44512

2020.10.09 33.80097

2020.10.08 48.61546

2020.10.07 48.58929

2020.10.06 97.97281

2020.10.05 58.23059

2020.10.04 25.56863

..

// using over

q)![t;();0b;] {x,(1#`$"yield",string y)!enlist(-;(%;`close;(xprev;y;`close));1)}/[()!();1+til 20]

date close yield1 yield2 yield3 yield4 yield5 ..

-----------------------------------------------------------------------------..

2020.10.23 54.97936 ..

2020.10.22 19.58467 -0.6437814 ..

2020.10.21 56.15261 1.867171 0.02133985 ..

2020.10.20 7.043811 -0.8745595 -0.6403406 -0.8718826 ..

2020.10.19 21.24007 2.015423 -0.6217438 0.08452541 -0.6136718 ..

2020.10.18 77.7882 2.662332 10.04348 0.3852999 2.971892 0.414862..

2020.10.17 48.44727 -0.37719 1.280937 5.877991 -0.1372214 1.473734..

2020.10.16 68.27999 0.4093671 -0.122232 2.214678 8.693614 0.215971..

2020.10.15 15.3227 -0.7755902 -0.6837243 -0.8030203 -0.278595 1.175342..

2020.10.14 53.50923 2.492155 -0.2163264 0.1044839 -0.3121164 1.519258..

2020.10.13 45.7328 -0.1453287 1.984645 -0.3302166 -0.05602929 -0.41208..

2020.10.12 0.8062521 -0.9823704 -0.9849325 -0.9473818 -0.988192 -0.98335..

2020.10.11 10.39343 11.89105 -0.7727357 -0.8057637 -0.3216968 -0.84778..

2020.10.10 10.44512 0.004972749 11.95515 -0.7716056 -0.8047978 -0.31832..

2020.10.09 33.80097 2.236055 2.252147 40.92357 -0.2609032 -0.36831..

2020.10.08 48.61546 0.438286 3.654372 3.677517 59.29809 0.063032..

2020.10.07 48.58929 -0.0005383464 0.4375117 3.651866 3.674999 59.26563..

2020.10.06 97.97281 1.016346 1.01526 1.898521 8.379771 8.426414..

2020.10.05 58.23059 -0.4056454 0.1984244 0.1977792 0.722749 4.57491 ..

2020.10.04 25.56863 -0.5609072 -0.7390232 -0.4737804 -0.4740637 -0.24355..

// using each

q)![t;();0b;] (`$"yield",/:string d)!{(-;(%;`close;(xprev;x;`close));1)} each d:1+til 20