I have a table with time series data grouped by name (with some dates missing), and would like to transform the table by creating a column for each group containing that group's data. In other words, how do I go from this
x:([] name:`a`a`a`b`b`c`c;date:2000.01.01 2000.01.02 2000.01.03 2000.01.01 2000.01.02 2000.01.02 2000.01.03;val:1 2 3 1 2 3 1)
to this?
([] date:2000.01.01 2000.01.02 2000.01.03;a:1 2 3;b:1 2 0N;c:0N 3 1)
You want to pivot the data:
q)t:([] name:`a`a`a`b`b`c`c;date:2000.01.01 2000.01.02 2000.01.03 2000.01.01 2000.01.02 2000.01.02 2000.01.03;val:1 2 3 1
2 3 1)
q)t
name date val
-------------------
a 2000.01.01 1
a 2000.01.02 2
a 2000.01.03 3
b 2000.01.01 1
b 2000.01.02 2
c 2000.01.02 3
c 2000.01.03 1
q)P:asc exec distinct name from t // get distinct pivot items
q)P
`s#`a`b`c
q)pvt:exec P!(name!val)P by date:date from t
q)pvt
date | a b c
----------| -----
2000.01.01| 1 1
2000.01.02| 2 2 3
2000.01.03| 3 1
q)u:([date:2000.01.01 2000.01.02 2000.01.03] a:1 2 3;b:1 2 0N;c:0N 3 1)
q)pvt~u
1b
The kx website steps through a simple example and a more general solution: http://code.kx.com/q/cookbook/pivoting-tables/