Functional update - multivariable function with dynamic columns

Question

Any help with the following would be much appreciated!

I have two tables: table1 is a summary table whilst table2 is a list of all data points. I want to be able to summarise the information in table2 for each row in table1.

table1:flip `grp`constraint!(`a`b`c`d; 10 10 20 20);
table2:flip `grp`cat`constraint`val!(`a`a`a`a`a`b`b`b;`cl1`cl1`cl1`cl2`cl2`cl2`cl2`cl1; 10 10 10 10 10 10 20 10; 1 2 3 4 5 6 7 8);

function:{[grpL;constraintL;catL] first exec total: sum val from table2 where constraint=constraintL, grp=grpL,cat=catL};

update cl1:function'[grp;constraint;`cl1], cl2:function'[grp;constraint;`cl2] from table1;

The fourth line of this code achieves what I want for the two categories:cl1 and cl2

In table1 I want to name a new column with the name of the category (cl1, cl2, etc.) and I want the values in that column to be the output from running the function over that column.

However, I have hundreds of different categories, so don't want to have to list them out manually as in the fourth line. How would I pass in a list of categories, e.g. below?

`cl1`cl2`cl3

Answer 1

Sticking to your approach, you would just have to make your update statement functional and then iterate over the columns like so:

{![`table1;();0b;(1#x)!enlist ((';function);`grp;`constraint;1#x)]} each `cl1`cl2

Assuming you can amend table1 in place. If you must retain the original table1 then you can pass it by value though it will consume more memory

{![x;();0b;(1#y)!enlist ((';function);`grp;`constraint;1#y)]}/[table1;`cl1`cl2]

Another approach would be to aggregate, pivot and join though it's not necessarily a better solution as you get nulls rather than zeros

a:select sum val by cat,grp,constraint from table2
p:exec (exec distinct cat from a)#cat!val by grp,constraint from a
table1 lj p