Allow user to choose kdb+ pivots from UI?

[quantific]

Hi,
I want to allow the user to dynamically choose the columns to pivot by in Pulse?
Is this possible?
Thanks.
q)

[admin]

Hi Quantific,

Let’s assume you have the kdb+ pivot code (https://stackoverflow.com/questions/30789471/pivot-table-in-kdb-q) and that:
trades_agg:1000#0!trades_agg
Then providing a UI to select columns is simple.

Example video: https://youtu.be/GyTQqKmymSU

Steps:

1. Add table trades_agg
2. Add a Multi-select component with sql query: ([] c:cols trades_agg)
3. Add a Multi-select component with sql query: ([] c:cols trades_agg)
4. Add a Dropdown select component with sql query: ([] c:cols trades_agg)
5. Add another table with the query: piv[trades_agg;(),<code>${{key1}}; (),${{key2}}; (),`${{key3}}]

i.e. You want three variables, then to place those 3 variables into the piv function while making sure they are lists of symbols.

// see https://stackoverflow.com/questions/30789471/pivot-table-in-kdb-q
// I then aggregate the data into equal sized buckets

<br /> trades_agg:1000#0!{<br /> qpd:5*2*4*”i”$16:00-09:30;<br /> date:raze(100*qpd)#’2009.01.05+til 5;<br /> sym:(raze/)5#enlist qpd#’100?`4;<br /> sym:(neg count sym)?sym;<br /> time:”t”$raze 500#enlist 09:30:00+15*til qpd;<br /> time+:(count time)?1000;<br /> exchange:raze 500#enlist raze(qpd div 3)#enlist<code>NYSE</code>NASDAQ`BATS;<br /> buysell:raze 500#enlist raze(qpd div 2)#enlist<code>B</code>S;<br /> shares:(500*qpd)?100;<br /> trades:([]date;sym;time;exchange;buysell;shares);<br /> select sum shares by date, sym, time: 15 xbar time.minute, exchange, buysell from trades}[];</p> <p>piv:{[t;k;p;v]<br /> / controls new columns names<br /> f:{[v;P]`${raze ” ” sv x} each string raze P[;0],’/:v,/:\:P[;1]};<br /> v:(),v; k:(),k; p:(),p; / make sure args are lists<br /> G:group flip k!(t:.Q.v t)k;<br /> F:group flip p!t p;<br /> key[G]!flip(C:f[v]P:flip value flip key F)!raze<br /> {[i;j;k;x;y]<br /> a:count[x]#x 0N;<br /> a[y]:x y;<br /> b:count[x]#0b;<br /> b[y]:1b;<br /> c:a i;<br /> c[k]:first'[a[j]@’where'[b j]];<br /> c}[I[;0];I J;J:where 1<>count'[I:value G]]/:\:[t v;value F]};</p> <p>/piv[trades_agg;<code>date</code>sym<code>time;</code>exchange<code>buysell;</code>shares]</p> <p>

[Author]

Posts