CODEWITHSUNDEEP
kdb+q-query
user2696565
user2696565

Reputation: 629

q Query: creating grid from a table

I have written a function that wraps a q query that returns me a table with three columns - c1,c2 and vals. How do I create a grid (2D array) such that I have c1 as x axis, c2 as y axis and values in the corresponding cells. For example, if one of the row in the table is c1:A c2:B value:100, I want to display A on x axis, B on y axis and 100 in the cell corresponding to (A,B).

I appreciate your help.

Input:
_c1_¦_c2_¦vals
===============-
_A_¦_B_¦ 100
_A_¦_Y_¦ 110
_X_¦_Y_¦ 120
_X_¦_T_¦ 220

Output:
_ _¦_B_¦_Y_¦_T_¦
===============-
_A_¦100¦ 110¦000¦
_X_¦000¦ 120¦220¦

Apologize for bad formatting, I'm not sure how to create a proper table.

Upvotes: 1

Views: 241

Answers (1)

WooiKent Lee
WooiKent Lee

Reputation: 1311

You can try to pivot your resultant table:

q)tt:flip`c1`c2`vals!(`a`a`x`x;`b`y`y`t;100 200 300 400)
q)tt
c1 c2 vals
----------
a  b  100
a  y  200
x  y  300
x  t  400
q)0^exec (distinct tt`c2)#c2!vals by c1:c1 from tt
c1| b   y   t
--| -----------
a | 100 200 0
x | 0   300 400

You can look at Pivot Table for more information.

Upvotes: 3

Related Questions