CODEWITHSUNDEEP
kdb+
amandath
amandath

Reputation: 83

Functional update - multivariable function with dynamic columns

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

Upvotes: 3

Views: 1007

Answers (3)

Karan Patel
Karan Patel

Reputation: 23

This approach is the easiest way to pass in a list of categories

{table1^flip x!function'[table1`grp;table1`constraint;]each x}`cl1`cl2

Upvotes: -1

terrylynch
terrylynch

Reputation: 13657

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

Upvotes: 5

Sean O'Hagan
Sean O'Hagan

Reputation: 1697

There are several different methods you can look into. The easiest method would be a functional update - http://code.kx.com/wiki/JB:QforMortals2/queries_q_sql#Functional_update

Below, though, should somewhat prove more useful, quicker and neater:

Your problem can be split into 2 parts. For the first part, you are looking to create a sum of each category by grp and constraint within table2. As for the second part, you are looking to join these results (the lookups) onto the corresponding records from table1.

You can create the necessary groups using by

q)exec val,cat by grp,constraint from table2
grp constraint| val       cat
--------------| ------------------------------
a   10        | 1 2 3 4 5 `cl1`cl1`cl1`cl2`cl2
b   10        | 6 8       `cl2`cl1
b   20        | ,7        ,`cl2

Note though, this will only create nested lists of the columns in your select query

Next is to sum each of the cat groups

q)exec sum each val group cat by grp,constraint from table2
grp constraint|
--------------| ------------
a   10        | `cl1`cl2!6 9
b   10        | `cl2`cl1!6 8
b   20        | (,`cl2)!,7

Then, to create the cat's columns you can use a pivot like syntax - http://code.kx.com/wiki/Pivot

q)cats:asc exec distinct cat from table2
q)exec cats#sum each val group cat by grp,constraint from table2
grp constraint| cl1 cl2
--------------| -------
a   10        | 6   9
b   10        | 8   6
b   20        |     7

Now you can use this lookup table and index into each row from table1

q)(exec cats#sum each val group cat by grp,constraint from table2)[table1]
cl1 cl2
-------
6   9
8   6

To fill the nulls with zeros, use the carat symbol - http://code.kx.com/wiki/Reference/Caret

q)0^(exec cats#sum each val group cat by grp,constraint from table2)[table1]
cl1 cl2
-------
6   9
8   6
0   0
0   0

And now you can join on each row from table1 to your results using join-each

q)table1,'0^(exec cats#sum each val group cat by grp,constraint from table2)[table1]
grp constraint cl1 cl2
----------------------
a   10         6   9
b   10         8   6
c   20         0   0
d   20         0   0

HTH, Sean

Upvotes: 3

Related Questions