CODEWITHSUNDEEP
kdb+ticker
James
James

Reputation: 1260

How to run a pivot operation on a table selection within a ticker function in KDB+/Q

I am trying to run a pivot operation on a selection made within a ticker function as follows:

// Trades table
trades:(
 []time:`timespan$();
 sym:`symbol$();
 exch:`symbol$();
 side:`symbol$();
 price:`float$();
 size:`float$());

// Generic pivot function
piv:{[t;k;p;v]f:{[v;P]`${raze "_" sv x} each string raze P,'/:v};v:(),v; k:(),k; p:(),p;G:group flip k!(t:.Q.v t)k;F:group flip p!t p;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<>count'[I:value G]]/:\:[t v;value F]};

 // Ticker function that "ticks" every 5 seconds
if[not system"t";system"t 5000";
 .z.ts:{
    // Selection is made from the trades table with the index reset
    trds:0!select first_price:first price, last_price:last price, mean_size:avg size, volume:sum size, min_price:min price, max_price:max price by time:1 xbar time.second, exch, sym from trades;

     // Check if selection returns at least one row and run pivot   
    if[(count trds)>0; (
        ptrds:piv[`trds;`time;`exch`sym;`first_price`last_price`mean_size`min_price`max_price`volume];
        show ptrds;
    )];

 }];

However this does not function as the trds reference made in the pivot function is not picked up, presumably because the trds selection is not global, however whilst using trds (without) as a parameter the piv function somehow is not applied and returns the result as if no operation had been performed. In addition, having to create an additional variable with ptrds seems inneficcient and also throws a reference error. Could someone please advise me how I can go about implementing this logic in a canonical manner that is efficient. Thanks

Upvotes: 1

Views: 155

Answers (1)

Josh Robbins
Josh Robbins

Reputation: 96

I think the source of your error here is the () brackets in your final if statement. The problem is that, when multiple arguments are enclosed within brackets, the object becomes a list. In kdb, elements of a list are evaluated from right to left, e.g.

q)(a:10;a+15)     
'a                
  [0]  (a:10;a+15)
             ^    
q)(a+15;a:10)     
25 10

To solve your problem, simply remove the brackets such that it reads

if[(count trades)>0;ptrds:piv[...];show ptrds;]

Upvotes: 3

Related Questions