functional select

[js610308]

Hi,

I have a question based on a functional select. I’m trying to use a functional select to generalise a select query so I don’t have to write out all the column names.  Here’s an example of the problem:

//Create a table:
z:( sym:sym1sym2sym1sym1;b:5 6 7 8);

//Iterate over n to add 10 new columns (col_n), note the values are random numbers.
{[n] ![z;();0b;(enlist$(“col_”),string n)!(enlist(?;4;10))]} each 1+til 10

This gives:
sym b col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10
-------------------------------------------------------------------
sym1 5 7 8 6 4 7 9 7 1 7 0
sym2 6 6 3 4 2 7 2 9 8 2 5
sym1 7 8 0 6 5 6 4 3 3 0 8
sym1 8 1 7 5 7 2 4 2 1 8 9

//Now I wish to generalise a specific select query so that I don’t have to type out all the column names. Here’s what I want:
select cnt:count i,tot_b:sum b,wcol1:b wavg col_1,sum b*col_1 by sym from z    //note I would like this not just for col1 but all Cols:

sym | cnt tot_b wcol1 b
----| ------------------
sym1| 3 20 4.95 99
sym2| 1 6 6 36

//Define list of Column names in the table (from col1 to col 10):
Cols:$"col\_",/:string 1+til 10 //Below is the same query as the one above but now in functional form. i.e. it returns the same answer you see printed below: ?[z;();(enlist sym)!(enlist sym);(cnttot\_bwcol1col\_1)!((count;i);(sum;b);(wavg;b;col\_1);(sum;(\*;b;`col_1)))]

sym | cnt tot_b wcol1 col_1
----| ---------------------
sym1| 3 20 4.95 99
sym2| 1 6 6 36

//To generalise this for all columns (without typing repeatedly the column titles into a select statement), I tried this:
?[z;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string Cols),Cols)!(((count;`i);(sum;`b)),((wavg;`b),/:Cols),((sum;(*;`b)),/:Cols))]

q does not like this and returns a rank error. The error comes from the piece at the end…((sum;(*;`b)),/:Cols). If I take out the sum it works, but I cannot see why the sum here causes a problem.

//by the way, removing the sum function, we get:
?[z;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string Cols),Cols)!(((count;`i);(sum;`b)),((wavg;`b),/:Cols),(((*;`b)),/:Cols))]

sym | cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10
----| --------------------------------------------------------------------------------------------------------------------------------------------------------------------
sym1| 3 20 4.95 4.8 5.6 5.55 4.65 5.25 3.6 1.7 4.95 6.4 35 56 8 40 0 56 30 42 40 20 35 56 35 42 16 45 28 32 35 21 16 5 21 8 35 0 64 0 56 72
sym2| 1 6 6 3 4 2 7 2 9 8 2 5 ,36 ,18 ,24 ,12 ,42 ,12 ,54 ,48 ,12 ,30

col1_1, col_2 etc are 2 element lists for the first row and 1 element for the second row here, and so I would have thought assigning a sum to this should not be an issue.

Any thoughts?

thanks,
John.

[P_Bukowinski1]

Hi John

Fixed using composition, prob ‘nested dimensions’ caused vector to not sum properly…

q)?[zz;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string cnames),cnames)!(((count;`i);(sum;`b)),((wavg;`b),/:cnames),((('[sum;*]);`b),/:cnames))]

sym | cnt tot_b wcol1 wcol2 wcol3 wcol4 wcol5 wcol6 wcol7 wcol8 wcol9 wcol10 col1 col2 col3 col4 col5 col6 ..

----| -----------------------------------------------------------------------------------------------------..

sym1| 3 20 5.95 4.05 3.5 7.7 5.25 2.9 4.2 3.2 5.6 5.2 119 81 70 154 105 58 ..

sym2| 1 6 2 5 4 0 5 2 6 7 9 7 12 30 24 0 30 12 ..

q)(sum 5 7 8 * 9 6 4)

119

q)(sum 5 7 8 * 9 6 4) ~ ('[sum;*])[5 7 8;9 6 4]

1b

---

BUT i find this method a bit … clunky and as you can see, you don’t know what’s happening.

If you change your structure, things got much easier:

with below proposal, your calculation is as simple as :

ttt:0!select cnt:count i,tot_b:sum b, wcol:b wavg v, sum b*v by sym,gp from t

and presentation:

---

q)exec ((cnttot_b!(first cnt;first tot_b)),((ren distinct tttgp)#(ren gp)!wcol),((distinct tttgp)#gp!b)) by sym:sym from ttt

sym | cnt tot_b wcol1 wcol10 wcol2 wcol3 wcol4 wcol5 wcol6 wcol7 wcol8 wcol9 col1 col10 col2 col3 col4 col5..

----| -----------------------------------------------------------------------------------------------------..

sym1| 3 20 3.55 6.15 4.9 7.8 3.95 4.5 4.3 7.1 6.85 6.2 71 123 98 156 79 90 ..

sym2| 1 6 3 4 8 4 0 0 9 6 5 2 18 24 48 24 0 0 ..

q)?[zz;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string cnames),cnames)!(((count;`i);(sum;`b)),((wavg;`b),/:cnames),((('[sum;*]);`b),/:cnames))]

sym | cnt tot_b wcol1 wcol2 wcol3 wcol4 wcol5 wcol6 wcol7 wcol8 wcol9 wcol10 col1 col2 col3 col4 col5 col6 ..

----| -----------------------------------------------------------------------------------------------------..

sym1| 3 20 3.55 4.9 7.8 3.95 4.5 4.3 7.1 6.85 6.2 6.15 71 98 156 79 90 86 ..

sym2| 1 6 3 8 4 0 0 9 6 5 2 4 18 48 24 0 0 54 ..

Full Example:

---

z:( sym:sym1sym2sym1sym1;b:5 6 7 8);

cnames:`$“col”,/:string 1+til 10

rg:{?[4;10]}@'10#`

zz:z,'flip cnames!rg

ren:(‘[(`$@),[“w”];($:)]’)

t:symbgpv!/:raze flip[z[symb]],/:‘flip (cnames,’'rg)

q)zz

sym b col1 col2 col3 col4 col5 col6 col7 col8 col9 col10

---------------------------------------------------------

sym1 5 3 8 7 4 9 1 7 6 9 7

sym2 6 3 8 4 0 0 9 6 5 2 4

sym1 7 8 6 7 5 3 7 5 5 9 8

sym1 8 0 2 9 3 3 4 9 9 2 4

q)t

sym b gp v

--------------

sym1 5 col1 3

sym1 5 col2 8

sym1 5 col3 7

sym1 5 col4 4

sym1 5 col5 9

sym1 5 col6 1

sym1 5 col7 7

sym1 5 col8 6

sym1 5 col9 9

sym1 5 col10 7

sym2 6 col1 3

q)tt:0!exec (distinct t`gp)#gp!v by sym,b from t

::

q)tt

sym b col1 col2 col3 col4 col5 col6 col7 col8 col9 col10

---------------------------------------------------------

sym1 5 3 8 7 4 9 1 7 6 9 7

sym1 7 8 6 7 5 3 7 5 5 9 8

sym1 8 0 2 9 3 3 4 9 9 2 4

sym2 6 3 8 4 0 0 9 6 5 2 4

q)tt~`sym`b xasc zz

1b

Pat

2015-11-15 23:16 GMT+00:00 John Smith <js610308@gmail.com>:

Hi,

I have a question based on a functional select. I’m trying to use a functional select to generalise a select query so I don’t have to write out all the column names.  Here’s an example of the problem:

//Create a table:
z:( sym:sym1sym2sym1sym1;b:5 6 7 8);

//Iterate over n to add 10 new columns (col_n), note the values are random numbers.
{[n] ![z;();0b;(enlist$(“col_”),string n)!(enlist(?;4;10))]} each 1+til 10

This gives:
sym b col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10
-------------------------------------------------------------------
sym1 5 7 8 6 4 7 9 7 1 7 0
sym2 6 6 3 4 2 7 2 9 8 2 5
sym1 7 8 0 6 5 6 4 3 3 0 8
sym1 8 1 7 5 7 2 4 2 1 8 9

//Now I wish to generalise a specific select query so that I don’t have to type out all the column names. Here’s what I want:
select cnt:count i,tot_b:sum b,wcol1:b wavg col_1,sum b*col_1 by sym from z    //note I would like this not just for col1 but all Cols:

sym | cnt tot_b wcol1 b
----| ------------------
sym1| 3 20 4.95 99
sym2| 1 6 6 36

//Define list of Column names in the table (from col1 to col 10):
Cols:$"col\_",/:string 1+til 10 //Below is the same query as the one above but now in functional form. i.e. it returns the same answer you see printed below: ?[z;();(enlist sym)!(enlist sym);(cnttot\_bwcol1col\_1)!((count;i);(sum;b);(wavg;b;col\_1);(sum;(\*;b;`col_1)))]

sym | cnt tot_b wcol1 col_1
----| ---------------------
sym1| 3 20 4.95 99
sym2| 1 6 6 36

//To generalise this for all columns (without typing repeatedly the column titles into a select statement), I tried this:
?[z;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string Cols),Cols)!(((count;`i);(sum;`b)),((wavg;`b),/:Cols),((sum;(*;`b)),/:Cols))]

q does not like this and returns a rank error. The error comes from the piece at the end…((sum;(*;`b)),/:Cols). If I take out the sum it works, but I cannot see why the sum here causes a problem.

//by the way, removing the sum function, we get:
?[z;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string Cols),Cols)!(((count;`i);(sum;`b)),((wavg;`b),/:Cols),(((*;`b)),/:Cols))]

sym | cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10
----| --------------------------------------------------------------------------------------------------------------------------------------------------------------------
sym1| 3 20 4.95 4.8 5.6 5.55 4.65 5.25 3.6 1.7 4.95 6.4 35 56 8 40 0 56 30 42 40 20 35 56 35 42 16 45 28 32 35 21 16 5 21 8 35 0 64 0 56 72
sym2| 1 6 6 3 4 2 7 2 9 8 2 5 ,36 ,18 ,24 ,12 ,42 ,12 ,54 ,48 ,12 ,30

col1_1, col_2 etc are 2 element lists for the first row and 1 element for the second row here, and so I would have thought assigning a sum to this should not be an issue.

Any thoughts?

thanks,
John.

–

Submitted via Google Groups

[effbiae]

Tip… Off topic…

(enlist sym)!enlist sam;
is
(1#sym)!1#sam;
generalizing 
{(1#x)!1#y}[sym;sam]
or
d1:{!.(1#)/:x}   /untested - not at terminal.

d1symsam 

[js610308]

Thank you Pat & effbiae, 

As you suggested:

((('[sum;*]);`b),/:cnames))

worked. I was lost without it. As for the rest your example, thanks, but currently it’s a bit over my head, I need to look into it.

best regards,

John

[kuentang]

<o:p> </o:p>

>>> d1:{!.(1#)/:x}   /untested - not at terminal.<o:p></o:p>

>>> d1symsam <o:p></o:p>

<o:p> </o:p>

These should work.<o:p></o:p>

<o:p> </o:p>

q) {(!). 1#/:x}ab<o:p></o:p>

q) ((!). 1#/:)ab<o:p></o:p>

q) ((!). 1#')ab<o:p></o:p>

q) ((!). 1 cut)ab<o:p></o:p>

<o:p> </o:p>

Kim<o:p></o:p>

<o:p> </o:p>

Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von Jack Andrews
Gesendet: Montag, 16. November 2015 22:33
An: personal-kdbplus@googlegroups.com
Betreff: Re: [personal kdb+] functional select<o:p></o:p>

<o:p> </o:p>

Tip… Off topic…<o:p></o:p>

(enlist sym)!enlist sam;<o:p></o:p>

is<o:p></o:p>

(1#sym)!1#sam;<o:p></o:p>

generalizing <o:p></o:p>

{(1#x)!1#y}[sym;sam]<o:p></o:p>

or<o:p></o:p>

d1:{!.(1#)/:x}   /untested - not at terminal.<o:p></o:p>

d1symsam <o:p></o:p>

On Monday, November 16, 2015, Patryk Bukowinski <p.bukowinski@gmail.com> wrote:<o:p></o:p>

Hi John<o:p></o:p>

Fixed using composition, prob ‘nested dimensions’ caused vector to not sum properly…<o:p></o:p>

q)?[zz;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string cnames),cnames)!(((count;`i);(sum;`b)),((wavg;`b),/:cnames),((('[sum;*]);`b),/:cnames))]<o:p></o:p>

sym | cnt tot_b wcol1 wcol2 wcol3 wcol4 wcol5 wcol6 wcol7 wcol8 wcol9 wcol10 col1 col2 col3 col4 col5 col6 ..<o:p></o:p>

----| -----------------------------------------------------------------------------------------------------..<o:p></o:p>

sym1| 3   20    5.95  4.05  3.5   7.7   5.25  2.9   4.2   3.2   5.6   5.2    119  81   70   154  105  58   ..<o:p></o:p>

sym2| 1   6     2     5     4     0     5     2     6     7     9     7      12   30   24   0    30   12   ..<o:p></o:p>

<o:p> </o:p>

q)(sum 5 7 8 * 9 6 4)<o:p></o:p>

119<o:p></o:p>

<o:p> </o:p>

q)(sum 5 7 8 * 9 6 4) ~ ('[sum;*])[5 7 8;9 6 4]<o:p></o:p>

1b<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

--------------------------------------------------------------------------<o:p></o:p>

BUT i find this method a bit … clunky and as you can see, you don’t know what’s happening.<o:p></o:p>

If you change your structure, things got much easier:<o:p></o:p>

<o:p> </o:p>

with below proposal, your calculation is as simple as :<o:p></o:p>

<o:p> </o:p>

ttt:0!select cnt:count i,tot_b:sum b, wcol:b wavg v, sum b*v by sym,gp from t<o:p></o:p>

<o:p> </o:p>

and presentation:<o:p></o:p>

-------------------------------------<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

q)exec ((cnttot_b!(first cnt;first tot_b)),((ren distinct tttgp)#(ren gp)!wcol),((distinct tttgp)#gp!b)) by sym:sym from ttt<o:p></o:p>

sym | cnt tot_b wcol1 wcol10 wcol2 wcol3 wcol4 wcol5 wcol6 wcol7 wcol8 wcol9 col1 col10 col2 col3 col4 col5..<o:p></o:p>

----| -----------------------------------------------------------------------------------------------------..<o:p></o:p>

sym1| 3   20    3.55  6.15   4.9   7.8   3.95  4.5   4.3   7.1   6.85  6.2   71   123   98   156  79   90  ..<o:p></o:p>

sym2| 1   6     3     4      8     4     0     0     9     6     5     2     18   24    48   24   0    0   ..<o:p></o:p>

q)?[zz;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string cnames),cnames)!(((count;`i);(sum;`b)),((wavg;`b),/:cnames),((('[sum;*]);`b),/:cnames))]<o:p></o:p>

sym | cnt tot_b wcol1 wcol2 wcol3 wcol4 wcol5 wcol6 wcol7 wcol8 wcol9 wcol10 col1 col2 col3 col4 col5 col6 ..<o:p></o:p>

----| -----------------------------------------------------------------------------------------------------..<o:p></o:p>

sym1| 3   20    3.55  4.9   7.8   3.95  4.5   4.3   7.1   6.85  6.2   6.15   71   98   156  79   90   86   ..<o:p></o:p>

sym2| 1   6     3     8     4     0     0     9     6     5     2     4      18   48   24   0    0    54   ..<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

Full Example:<o:p></o:p>

-------------------------------------<o:p></o:p>

<o:p> </o:p>

z:( sym:sym1sym2sym1sym1;b:5 6 7 8);<o:p></o:p>

cnames:`$“col”,/:string 1+til 10<o:p></o:p>

rg:{?[4;10]}@'10#`<o:p></o:p>

zz:z,'flip cnames!rg<o:p></o:p>

ren:(‘[(`$@),[“w”];($:)]’)<o:p></o:p>

t:symbgpv!/:raze flip[z[symb]],/:‘flip (cnames,’'rg)<o:p></o:p>

<o:p> </o:p>

q)zz<o:p></o:p>

sym  b col1 col2 col3 col4 col5 col6 col7 col8 col9 col10<o:p></o:p>

---------------------------------------------------------<o:p></o:p>

sym1 5 3    8    7    4    9    1    7    6    9    7    <o:p></o:p>

sym2 6 3    8    4    0    0    9    6    5    2    4    <o:p></o:p>

sym1 7 8    6    7    5    3    7    5    5    9    8    <o:p></o:p>

sym1 8 0    2    9    3    3    4    9    9    2    4    <o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

q)t<o:p></o:p>

sym  b gp    v<o:p></o:p>

--------------<o:p></o:p>

sym1 5 col1  3<o:p></o:p>

sym1 5 col2  8<o:p></o:p>

sym1 5 col3  7<o:p></o:p>

sym1 5 col4  4<o:p></o:p>

sym1 5 col5  9<o:p></o:p>

sym1 5 col6  1<o:p></o:p>

sym1 5 col7  7<o:p></o:p>

sym1 5 col8  6<o:p></o:p>

sym1 5 col9  9<o:p></o:p>

sym1 5 col10 7<o:p></o:p>

sym2 6 col1  3<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

q)tt:0!exec (distinct t`gp)#gp!v by sym,b from t<o:p></o:p>

::<o:p></o:p>

q)tt<o:p></o:p>

sym  b col1 col2 col3 col4 col5 col6 col7 col8 col9 col10<o:p></o:p>

---------------------------------------------------------<o:p></o:p>

sym1 5 3    8    7    4    9    1    7    6    9    7    <o:p></o:p>

sym1 7 8    6    7    5    3    7    5    5    9    8    <o:p></o:p>

sym1 8 0    2    9    3    3    4    9    9    2    4    <o:p></o:p>

sym2 6 3    8    4    0    0    9    6    5    2    4    <o:p></o:p>

q)tt~symb xasc zz<o:p></o:p>

1b<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

<o:p> </o:p>

Pat<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

2015-11-15 23:16 GMT+00:00 John Smith <js610308@gmail.com>:<o:p></o:p>

Hi,

I have a question based on a functional select. I’m trying to use a functional select to generalise a select query so I don’t have to write out all the column names.  Here’s an example of the problem:

//Create a table:
z:( sym:sym1sym2sym1sym1;b:5 6 7 8);

//Iterate over n to add 10 new columns (col_n), note the values are random numbers.
{[n] ![z;();0b;(enlist$(“col_”),string n)!(enlist(?;4;10))]} each 1+til 10

This gives:
sym  b col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10

sym1 5 7     8     6     4     7     9     7     1     7     0     
sym2 6 6     3     4     2     7     2     9     8     2     5     
sym1 7 8     0     6     5     6     4     3     3     0     8     
sym1 8 1     7     5     7     2     4     2     1     8     9

//Now I wish to generalise a specific select query so that I don’t have to type out all the column names. Here’s what I want:
select cnt:count i,tot_b:sum b,wcol1:b wavg col_1,sum b*col_1 by sym from z    //note I would like this not just for col1 but all Cols:

sym	cnt tot_b wcol1 b
sym1	3   20    4.95  99
sym2	1   6     6     36

//Define list of Column names in the table (from col1 to col 10):
Cols:$"col\_",/:string 1+til 10 //Below is the same query as the one above but now in functional form. i.e. it returns the same answer you see printed below: ?[z;();(enlist sym)!(enlist sym);(cnttot\_bwcol1col\_1)!((count;i);(sum;b);(wavg;b;col\_1);(sum;(\*;b;`col_1)))]

sym	cnt tot_b wcol1 col_1
sym1	3   20    4.95  99
sym2	1   6     6     36

//To generalise this for all columns (without typing repeatedly the column titles into a select statement), I tried this:
?[z;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string Cols),Cols)!(((count;`i);(sum;`b)),((wavg;`b),/:Cols),((sum;(*;`b)),/:Cols))]

q does not like this and returns a rank error. The error comes from the piece at the end…((sum;(*;`b)),/:Cols). If I take out the sum it works, but I cannot see why the sum here causes a problem.

//by the way, removing the sum function, we get:
?[z;();(enlist `sym)!(enlist `sym);(`cnt`tot_b,(`$“w”,/:string Cols),Cols)!(((count;`i);(sum;`b)),((wavg;`b),/:Cols),(((*;`b)),/:Cols))]

sym	cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1   col_2   col_3    col_4    col_5    col_6    col_7    col_8  col_9   col_10
sym1	3   20    4.95   4.8    5.6    5.55   4.65   5.25   3.6    1.7    4.95   6.4     35 56 8 40 0 56 30 42 40 20 35 56 35 42 16 45 28 32 35 21 16 5 21 8 35 0 64 0 56 72
sym2	1   6     6      3      4      2      7      2      9      8      2      5       ,36     ,18     ,24      ,12      ,42      ,12      ,54      ,48    ,12     ,30

col1_1, col_2 etc are 2 element lists for the first row and 1 element for the second row here, and so I would have thought assigning a sum to this should not be an issue.

Any thoughts?

thanks,<o:p></o:p>

John.<o:p></o:p>

–
Submitted via Google Groups

[Aaron_Davies]

To: personal-kdbplus@googlegroups.comX-Mailer: Apple Mail (2.2104)> ?[z;();(enlist sym)!(enlist sym);(cnttot_bwcol1col_1)!((count;i);(sum;b);(wavg;b;col_1);(sum;(*;b;col_1)))]> > sym | cnt tot_b wcol1 col_1> ----| ---------------------> sym1| 3 20 4.95 99 > sym2| 1 6 6 36> > //To generalise this for all columns (without typing repeatedly the column titles into a select statement), I tried this:> ?[z;();(enlist sym)!(enlist sym);(cnttot_b,($"w",/:string Cols),Cols)!(((count;i);(sum;b)),((wavg;b),/:Cols),((sum;(*;b)),/:Cols))]\> \> q does not like this and returns a rank error.take a look at the parse trees you're generating:q)0N!last((wavg;b),/:Cols),((sum;(*;b)),/:Cols);(sum;(*;b);col_10)q)this is not the same thing as (sum;(*;b;col_1))(the 'rank error is coming from the (*;b) – * is dyadic, and you’re only providing one argument)this will work:q)?[z;();(enlist sym)!(enlist sym);(cnttot_b,($"w",/:string Cols),Cols)!(((count;i);(sum;b)),(wavg;b),/:Cols),(sum),'enlist each(*;b),/:Cols]sym | cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10----| ---------------------------------------------------------------------------------------------------------------------------------------------sym1| 3 20 7.15 3.5 7 0.9 5.8 5.25 3.2 5.9 3.25 2.85 143 70 140 18 116 105 64 118 65 57 sym2| 1 6 1 6 5 7 2 1 5 8 1 8 6 36 30 42 12 6 30 48 6 48 q)though when your query gets this complicated, it's usually clearer to explicitly write out the parse tree generator you're looking for:q)?[z;();(enlist sym)!(enlist sym);(cnttot_b,($“w”,/:string Cols),Cols)!(((count;i);(sum;b)),(wavg;b),/:Cols),{(sum;(*;b;x))}each Cols]sym | cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10----| ---------------------------------------------------------------------------------------------------------------------------------------------sym1| 3 20 7.15 3.5 7 0.9 5.8 5.25 3.2 5.9 3.25 2.85 143 70 140 18 116 105 64 118 65 57 sym2| 1 6 1 6 5 7 2 1 5 8 1 8 6 36 30 42 12 6 30 48 6 48 q)and probably also a good idea to reorganize the whole thing into the different types of operations you’re doing:q)?[z;();(enlistsym)!enlistsym;(cnttot_b!((count;i);(sum;b))),(($"w",'string Cols)!(wavg;b),/:Cols),Cols!{(sum;(*;b;x))}each Cols]sym | cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10----| ---------------------------------------------------------------------------------------------------------------------------------------------sym1| 3 20 7.15 3.5 7 0.9 5.8 5.25 3.2 5.9 3.25 2.85 143 70 140 18 116 105 64 118 65 57 sym2| 1 6 1 6 5 7 2 1 5 8 1 8 6 36 30 42 12 6 30 48 6 48 q)and maybe even wrap the dictionary creation in the last bit into the function:q)?[z;();(enlistsym)!enlistsym;(cnttot_b!((count;i);(sum;b))),(($“w”,'string Cols)!(wavg;b),/:Cols),{x!{(sum;(*;b;x))}each x}Cols]sym | cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10----| ---------------------------------------------------------------------------------------------------------------------------------------------sym1| 3 20 7.15 3.5 7 0.9 5.8 5.25 3.2 5.9 3.25 2.85 143 70 140 18 116 105 64 118 65 57 sym2| 1 6 1 6 5 7 2 1 5 8 1 8 6 36 30 42 12 6 30 48 6 48 q)you could continue to add abstraction layers, depending on how often you need to repeat this kind of thing and how far from the “obvious” way to write the code you’re willing to gohere’s one where you give a list of pairs of label and calculation functions together with the list of columns to apply them to, and get back the parse tree to run to implement them:q)?[z;();(enlistsym)!enlistsym;(cnttot_b!((count;i);(sum;b))),raze(($"w",'string@;(wavg;b),/:);(::;{(sum;(*;b;x))}')){(x[0]y)!x[1]y}\:Cols]sym | cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10----| ---------------------------------------------------------------------------------------------------------------------------------------------sym1| 3 20 7.15 3.5 7 0.9 5.8 5.25 3.2 5.9 3.25 2.85 143 70 140 18 116 105 64 118 65 57 sym2| 1 6 1 6 5 7 2 1 5 8 1 8 6 36 30 42 12 6 30 48 6 48 q)finally, i should of course note that you could be using wsum here :)q)?[z;();(enlist sym)!(enlist sym);(cnttot_b,($“w”,/:string Cols),Cols)!(((count;i);(sum;b)),(wavg;b),/:Cols),(wsum;b),/:Cols]sym | cnt tot_b wcol_1 wcol_2 wcol_3 wcol_4 wcol_5 wcol_6 wcol_7 wcol_8 wcol_9 wcol_10 col_1 col_2 col_3 col_4 col_5 col_6 col_7 col_8 col_9 col_10----| ---------------------------------------------------------------------------------------------------------------------------------------------sym1| 3 20 7.15 3.5 7 0.9 5.8 5.25 3.2 5.9 3.25 2.85 143 70 140 18 116 105 64 118 65 57 sym2| 1 6 1 6 5 7 2 1 5 8 1 8 6 36 30 42 12 6 30 48 6 48 q)