0

We know we can write a function like

select avg val by category from tab

But what if I need to write a complicated customized functions like

select myfunc by category from tab

where here myfund will compute with multiple columns in the tab. for example inside myfunc I might do another layer of select by, might do some filtering, etc. As a basic example how do I wrap below of a+b+c+d

select a+b+c+d by category from tab

inside a myfunc, where it has visibility into columns a, b, c, and d, and will do some manipulation with them?

Will Da Silva
  • 6,386
  • 2
  • 27
  • 52
Matt Frank
  • 177
  • 6

2 Answers2

1

You can pass the columns into your function in a tabular format, e.g:

q)t:([]col1:10?`a`b`c;col2:10?10;col3:10?1f;col4:10?.z.D)
q)select {break}[([]col2;col3;col4)] by col1 from t
'break
  [1]  {break}
          ^
q))x
col2 col3      col4
-------------------------
9    0.5785203 2008.02.04
7    0.1959907 2003.07.05
8    0.6919531 2007.12.27

If you're going to use all columns inside of your functions then another approach is to group the table into subtables and run a function for each subtable:

func each t group t`col1
terrylynch
  • 11,844
  • 13
  • 21
1

You can replace avg quite easily with your own function like so:

select {[a;b;c;d]a+b+c+d}[a;b;c;d] by category from tab

If you want to do it row by row use each-both '

select {[a;b;c;d]a+b+c+d} ' [a;b;c;d] by category from tab

Could you provide an example of what you are trying to achieve with the additional by/filtering inside the function? Doesn't seem to me like the best approach

Matt Moore
  • 2,705
  • 6
  • 13
  • actually i gave it more thoughts. Ithink you are right. a better approach is to unpivot the table to put the columns into rows and do a select by from there. nevertheless your answer is helpful! – Matt Frank Oct 29 '20 at 18:18