0

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.

Will
  • 910
  • 7
  • 17

1 Answers1

1

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

terrylynch
  • 11,844
  • 13
  • 21
  • thanks a lot Terry. It's more concise and more readable so that's obviously the way to go. will test tomorrow. only problem is I still don't know what was wrong with my expression, I guess i can live with it... thanks! – Will Jan 06 '21 at 19:16
  • 1
    Your dmv shouldn't have had a ";" at the end and you should have placed brackets around it and valued it rather than parsing it. This works ```?[r;();0b;k!c,value"(",(-1_dmv),")"]``` – terrylynch Jan 06 '21 at 21:32