Pivot table with columns as year / date in KDB +

Question

I am trying to create a pivot table with columns like year from a simple table

 q)growth:([] stock:asc 9#`goog`apple`nokia; year: 9#2015 2016 2017; returns:9?20 ) q)growth stock year returns ------------------ apple 2015 9 apple 2016 18 apple 2017 17 goog 2015 8 goog 2016 13 goog 2017 17 nokia 2015 12 nokia 2016 12 nokia 2017 2 

but I can’t get the correct structure, it still returns a dictionary to me, and not a few year columns.

 q)exec (distinct growth`year)#year!returns by stock:stock from growth stock| -----| ---------------------- apple| 2015 2016 2017!9 18 17 goog | 2015 2016 2017!8 13 17 nokia| 2015 2016 2017!12 12 2 

Am I doing something wrong?

Answer 1

Table column names in KDB must be characters, not any other data types.

In the pivot table, the data type of the year column is int \ long, so the reason is that the correct table does not open.

If you type it as a symbol, then it will work.

 q)growth:([] stock:asc 9#`goog`apple`nokia; year: 9#2015 2016 2017; returns:9?20 ) q)growth:update `$string year from growth q)exec (distinct growth`year)#year!returns by stock:stock from growth stock| 2015 2016 2017 -----| -------------- apple| 9 18 17 goog | 8 13 17 nokia| 12 12 2 

Alternatively, you can switch the columns of the pivot table to "stock" rather than "year" and get a pivot table with the same source table.

 q)growth:([] stock:asc 9#`goog`apple`nokia; year: 9#2015 2016 2017; returns:9?20 ) q)show exec (distinct growth`stock)#stock!returns by year:year from growth year| apple goog nokia ----| ---------------- 2015| 4 2 4 2016| 5 13 12 2017| 12 6 1 

Answer 2

You need to convert years to characters in order to use them as column headers. In this case, I first updated the growth table, then rotated:

 q)exec distinct[year]#year!returns by stock:stock from update `$string year from growth stock| 2015 2016 2017 -----| -------------- apple| 12 8 10 goog | 1 9 11 nokia| 5 6 1 

In addition, you can see that I changed to distinct[year] from (distinct growth`year) , as this gives the same result when outputting year from the updated table.