0

I have below code. weight is from the right table, and the aggregation works. weightLeft is from the left table, and the aggregation doesn't work. Is it possible to make weightLeft works?

t:([]sym:3#`ibm;time:10:01:01 10:01:04 10:01:08;price:200 201 205;weightLeft:3 4 5);

price:101 103 103 104 104 107 108 107 108;
weight:10 20 30 10 20 30 10 20 30;
q:([]sym:`ibm; time:10:01:01+til 9; price:price; weight:weight);
f:`sym`time;
w:-2 1+\:t.time;

wj[w;f;t;(q;(sum;`price);({sum x*y};`price;`weight))]; // OK
wj[w;f;t;(q;(sum;`price);({sum x*y};`price;`weightLeft))]; // failed
H42
  • 725
  • 2
  • 9
  • 28

1 Answers1

1

I do not think it is possible to use columns from the left table in that manner. If you have use the identity operator :: as your aggregation function you can see how kdb interprets that column as a null symbol, hence the type error.

q)wj[w;f;t;(q;(sum;`price);(::;`weightLeft))]
sym time     price weightLeft
-----------------------------
ibm 10:01:01 204   ``
ibm 10:01:04 414   ````
ibm 10:01:08 430   ````

For your use case could you aggregate on price then multiply by weightLeft afterwards?

q)update price*weightLeft from wj[w;f;t;(q;(sum;`price))]
sym time     price weightLeft
-----------------------------
ibm 10:01:01 612   3
ibm 10:01:04 1656  4
ibm 10:01:08 2150  5
Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
  • 1
    thanks for your reply, but they are not exactly the same. I need to do sum(a*b), but by doing this way it's sum(a)*sum(b), so they are different... – H42 Apr 20 '23 at 09:05