CODEWITHSUNDEEP
kdb+
derenik
derenik

Reputation: 163

kdb: dynamically denormalize a table (convert key values to column names)

I have a table like this:

q)t:([sym:(`EURUSD`EURUSD`AUDUSD`AUDUSD);server:(`S01`S02`S01`S02)];volume:(20;10;30;50))
q)t
sym    server| volume
-------------| ------
EURUSD S01   | 20    
EURUSD S02   | 10    
AUDUSD S01   | 30    
AUDUSD S02   | 50

I need to de-normalize it to display the data nicely. The resulting table should look like this:

sym   | S01 S02
------| -------
EURUSD| 20  10 
AUDUSD| 30  50

How do I dynamically convert the original table using distinct values from server column as column names for the new table?

Thanks!

Upvotes: 3

Views: 952

Answers (2)

nyi
nyi

Reputation: 3229

One tricky thing around pivoting a table is - the keys of the dictionary should be of type symbol otherwise it won't generate the pivot table structure.

E.g. In the following table, we have a column dt with type as date.

t:([sym:(`EURUSD`EURUSD`AUDUSD`AUDUSD);dt:(0 1 0 1+.z.d)];volume:(20;10;30;50))

Now if we want to pivot it with columns as dates , it will generate a structure like : 

q)P:asc exec distinct dt from t
q)exec P#(dt!volume) by sym:sym from t
(`s#flip (enlist `sym)!enlist `s#`AUDUSD`EURUSD)!((`s#2018.06.22 2018.06.23)!30j, 50j;(`s#2018.06.22 2018.06.23)!20j, 10j)

To get the dates as the columns , the dt column has to be typecasted to symbol : 

show P:asc exec distinct `$string date from t
`s#`2018.06.22`2018.06.23

q)exec P#((`$string date)!volume) by sym:sym from t
sym   | 2018.06.22 2018.06.23
------| ---------------------
AUDUSD| 30         50
EURUSD| 20         10

Upvotes: 1

Rahul
Rahul

Reputation: 3969

Basically you want 'pivot' table. Following page has a very good solution for your problem: http://code.kx.com/q/cookbook/pivoting-tables/

Here are the commands to get the required table:

  q) P:asc exec distinct server from t
  q) exec P#(server!volume) by sym:sym from t

Upvotes: 4

Related Questions