1

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.

thefourtheye
  • 233,700
  • 52
  • 457
  • 497
user2696565
  • 587
  • 1
  • 8
  • 17

1 Answers1

3

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.

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
WooiKent Lee
  • 1,301
  • 6
  • 4