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.
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.