title: Pivot table in kdb+/q
body: <p>I'm trying to pivot some trade data in KDB/q. Although my data are only slightly different from the working example on the website (see the general pivot function: <a href="http://code.kx.com/q/cookbook/pivoting-tables/" rel="nofollow noreferrer">http://code.kx.com/q/cookbook/pivoting-tables/</a>),
  I can't get the function to work, even after several hours of trying (I'm very new to KDB).</p>
  
  <p>Put simply, I'm trying to go from this table:</p>
  
  <pre><code>q)5# trades_agg
  date       sym  time  exchange buysell| shares
  --------------------------------------| ------
  2009.01.05 aaca 09:30 BATS     B      | 484
  2009.01.05 aaca 09:30 BATS     S      | 434
  2009.01.05 aaca 09:30 NASDAQ   B      | 235
  2009.01.05 aaca 09:30 NASDAQ   S      | 429
  2009.01.05 aaca 09:30 NYSE     B      | 309
  </code></pre>
  
  <p>to this one:</p>
  
  <pre><code>date       sym  time  | BATSsharesB BATSsharesS NASDAQsharesB    ... 
  ----------------------| -----------------------------------------------
  2009.01.05 aaca 09:30 | 484          434        235              ...
  ...                   | ... 
  </code></pre>
  
  <p>I'll provide a working example to illustrate things:</p>
  
  <pre><code>// Create data
  qpd:5*2*4*"i"$16:00-09:30
  date:raze(100*qpd)#'2009.01.05+til 5
  sym:(raze/)5#enlist qpd#'100?`4
  sym:(neg count sym)?sym
  time:"t"$raze 500#enlist 09:30:00+15*til qpd
  time+:(count time)?1000
  exchange:raze 500#enlist raze(qpd div 3)#enlist`NYSE`NASDAQ`BATS
  buysell:raze 500#enlist raze(qpd div 2)#enlist`B`S
  shares:(500*qpd)?100
  trades:([]date;sym;time;exchange;buysell;shares)
  //I then aggregate the data into equal sized buckets
  trades_agg: select sum shares by date, sym, time: 15 xbar time.minute, exchange, buysell from trades
  
  // pivot function from the code.kx.com website
  piv:{[t;k;p;v;f;g]
   v:(),v;
   G:group flip k!(t:.Q.v t)k;
   F:group flip p!t p;
   count[k]!g[k;P;C]xcols 0!key[G]!flip(C:f[v]P:flip value flip key F)!raze
    {[i;j;k;x;y]
     a:count[x]#x 0N;
     a[y]:x y;
     b:count[x]#0b;
     b[y]:1b;
     c:a i;
     c[k]:first'[a[j]@'where'[b j]];
     c}[I[;0];I J;J:where 1&lt;&gt;count'[I:value G]]/:\:[t v;value F]}
  </code></pre>
  
  <p>I subsequently apply this pivot function to the example with the functions f and g set to their default (::) values but I get an error message:</p>
  
  <pre><code>piv[`trades_agg;`date`sym`time;`exchange`buysell;`shares;(::);(::)]
  </code></pre>
  
  <p>Even when I use the suggested f and g functions it doesn't work:</p>
  
  <pre><code> f:{[v;P]`$raze each string raze P[;0],'/:v,/:\:P[;1]}
   g:{[k;P;c]k,(raze/)flip flip each 5 cut'10 cut raze reverse 10 cut asc c}
  </code></pre>
  
  <p>I don't get why this is not working correctly since it is so close to the example on the website.</p>