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
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
yield1yield2!((-;(%;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
Hello, Matt
This is awesome! I will have a try.
Thank you so much and have a good day!
James
For table construction:
q)flip (dateclose!(“d”$();“f”$())), “f”$($"yield" ,/: string 1+til 20)!() date close yield1 yield2 yield3 yield4 yield5 yield6 yield7 yield8 yield9 yie.. -----------------------------------------------------------------------------.. q)meta flip (dateclose!("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 |
| .. |
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
Thank you Jason.
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
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..
..