I'm trying to build a generic query, where I would not know the name and number of columns of a table. so assuming I have a table such as:
r:([] a:til 5; b:til 5; c:til 5);
I would like to build a query to calculate the difference from the mean:
c:cols[r] except `date;
select a, b, c, a_dm:(a - avg a) , b_dm:(b - avg b), c_dm:(c - avg c)from r;
so my understanding is I should use the functional form ie:
?[r;();0b;`a`b`c`a_dm`b_dm`c_dm!(`a;`b;`c;(-;`a;(avg;`a));(-;`b;(avg;`b));(-;`c;(avg;`c)))]
and since I do not know which table I will receive nor its column names I need to have something generic so I tried something on the lines of:
dmk:`$(string c),\:"_dm";
k:c,dmk;
dmv:raze "(-;",/:("`",/:string c),/'";(avg;",/:("`",/:string c),\:"));"
parse dmv
count dmv
v:c,parse dmv
dic:k!v
?[r;();0b;dic]
which does not work I guess because my dmv is not ok.
I am totally unclear how to fix it. if anyone could please give me some pointers, would be greatly appreciated.
Although the column order isn't quite the same, you could try something like this:
q)f:{(x,`$string[x],"_dm")!(x;(-;x;(avg;x)))}
q)
q)?[r;();0b;raze f each cols r]
a a_dm b b_dm c c_dm
--------------------
0 -2 0 -2 0 -2
1 -1 1 -1 1 -1
2 0 2 0 2 0
3 1 3 1 3 1
4 2 4 2 4 2
Create a function which creates your per-column where clause dictionary, then apply to all columns