Pivot

Community Support
1

Is there an easy way to pivot a table by one of its columns? Forexample, taking the canonical trade:(time:time$();sym:symbol$();price:float$();size:int$()), how would one create a table with 30minute buckets in each row, the list of symbols across the top and thevolume in each of the columns? I know I can probably do it bycreating a dictionary of the symbols, filling each with the results ofa query and subsequently flipping it, but if there is a more efficientway, I’d love to hear it.Thanks,Victor

2

charset=us-ascii;
format=flowed;
delsp=yes

Look up functional select in Q for Mortals.

Regards,
Attila

3

Hello Victor,Below is a example:n:1000syms:FDPIBMMSFTstart:09:30:00.000close:16:30:00.000trade:([]time:asc start+n?(close-start);sym:n?syms;price:n?100.0;size:n?1000)pivot:exec syms!(sym!size)syms by bucket:30 xbar time.minute fromtradeThe syntax may seem a little tricky but it's based around the idea ofbuilding dictionaries from your query using !.As Attila mentions you can use functional select which you could thenpossibly wrap in a custom function for pivoting.Regards,Fintan.On Nov 20, 6:31?pm, Victor Wong <victor.w...> wrote:&gt; Is there an easy way to pivot a table by one of its columns? ?For&gt; example, taking the canonical trade:([]time:time$();sym:symbol$&gt; ();price:float$();size:`int$()), how would one create a table with 30> minute buckets in each row, the list of symbols across the top and the> volume in each of the columns? ?I know I can probably do it by> creating a dictionary of the symbols, filling each with the results of> a query and subsequently flipping it, but if there is a more efficient> way, I’d love to hear it.>> Thanks,> Victor</victor.w…>

4

Thank you for the example. This is very helpful. Thanks.VictorOn Nov 23, 7:47?am, fintanq <fint…> wrote:> Hello Victor,>> Below is a example:>> n:1000> syms:FDPIBMMSFT&gt; start:09:30:00.000&gt; close:16:30:00.000&gt; trade:([]time:asc start+n?(close-start);sym:n?syms;price:n?&gt; 100.0;size:n?1000)pivot:exec syms!(sym!size)syms by bucket:30 xbar time.minute from&gt; trade&gt;&gt; The syntax may seem a little tricky but it's based around the idea of&gt; building dictionaries from your query using !.&gt;&gt; As Attila mentions you can use functional select which you could then&gt; possibly wrap in a custom function for pivoting.&gt;&gt; Regards,&gt;&gt; Fintan.&gt;&gt; On Nov 20, 6:31?pm, Victor Wong <victor.w...> wrote:&gt;&gt;&gt;&gt; &gt; Is there an easy way topivota table by one of its columns? ?For&gt; &gt; example, taking the canonical trade:([]time:time$();sym:symbol$&gt; &gt; ();price:float$();size:`int$()), how would one create a table with 30> > minute buckets in each row, the list of symbols across the top and the> > volume in each of the columns? ?I know I can probably do it by> > creating a dictionary of the symbols, filling each with the results of> > a query and subsequently flipping it, but if there is a more efficient> > way, I’d love to hear it.>> > Thanks,> > Victor</victor.w…></fint…>