cancel
Showing results for
Did you mean:

## Accumulators - Access additional list / column

New Contributor III

I have a table created like below.

``t:([]c: 30 40 25 20 4 4; c1: 10 20 5 25 5 4)``

c2 is calculated Column (value from c1 or prev c2 is used based on evaluation).

prev value is taken as 0 for first row / if not available

Below are the calculations.

 c c1 c2 c1>prev c2 OR prev c < prev c2 ? c1 prev c2 30 10 10 10>0 (True) OR No need of evaluation ? 10 40 20 20 20>10(True) OR No need of evaluation ? 20 25 5 20 5>20(False) OR 40<20(False) ? 20 20 25 25 25>20(True) OR No need of evaluation ? 25 4 5 5 5>25(False) OR 20<25(True) ? 5 4 4 4 4>5(False) OR 4<5(True) ? 4

What I have till now

``update c2: 0{?[y>x;y;0N]}\c1 from t``

How can I OR the condition prev c < prev c2 along with this? Can column c be accessed? Something like below.

``update c2: 0{?[y>x|0^c <x;y;x]}\c1 from t   ``

2 ACCEPTED SOLUTIONS
Valued Contributor

Yes in that case an accumulator is needed. One method you could choose would be to pass a table through to accumulate while also allowing you to look back to previous rows:

``````q)update c2:1_@[;`c2]{y[`c2]:enlist \$[(y[`c1][0]>last x[`c2]) or ((last x[`c])<last x[`c2]);y[`c1][0];last x`c2];x,y}/[enlist each {(1#0#x),x}update c2:0 from `c`c1#t] from t
c   c1  c2
----------
30  10  10
40  20  20
25  5   20
20  25  25
4   5   5
4   4   4
4.5 3   4
4.5 3.5 4``````
New Contributor III

I was trying something like below. I am not sure if it works in all scenarios.

I will validate your solution as well and revert soon.

Thanks much for your patience. KX community support and response is just excellent 👍

``````update c2: {?[((y>x)|(z<x));y;x]}\[0;c1;0^prev c] from t
c   c1  c2
----------
30  10  10
40  20  20
25  5   20
20  25  25
4   5   5
4   4   4
4.5 3   4
4.5 3.5 4``````

9 REPLIES 9
Valued Contributor

Looking at your expected `c2` this logic may be what you are looking for:

``````q)update c2:fills ?[(c1>prev c1) or c<prev c1;c1;0N] from t
c  c1 c2
--------
30 10 10
40 20 20
25 5  20
20 25 25
4  5  5
4  4  4``````

New Contributor III

Thanks rocuinneagain,

Works for most scenarios. I have added one more row at the end where I get a different result than expected. The value for c2 in last row should be 4 instead of 3.

``t:([]c: 30 40 25 20 4 4 1; c1: 10 20 5 25 5 4 3)``

 c c1 c2 c1>prev c2 OR prev c < prev c2 ? c1 prev c2 30 10 10 10>0 (True) OR No need of evaluation ? 10 40 20 20 20>10(True) OR No need of evaluation ? 20 25 5 20 5>20(False) OR 40<20(False) ? 20 20 25 25 25>20(True) OR No need of evaluation ? 25 4 5 5 5>25(False) OR 20<25(True) ? 5 4 4 4 4>5(False) OR 4<5(True) ? 4 1 3 4 3>4(False) OR 4<4(False) ? 4

``````update c2:fills ?[(c1>prev c1) or c<prev c1;c1;0N] from t
c  c1 c2
--------
30 10 10
40 20 20
25 5  20
20 25 25
4  5  5
4  4  4
1  3  3``````

Valued Contributor

changing `c` to `prev[c]` looks to be what was missing

``````q)update c2:fills ?[(c1>prev c1) or prev[c]<prev c1;c1;0N] from t
c  c1 c2
--------
30 10 10
40 20 20
25 5  20
20 25 25
4  5  5
4  4  4
1  3  4``````

New Contributor III

Somehow not being able to use the previous calculated value of c2 directly is causing fallout for different combination of data. Last value of c2 will be 4 as per the logic but we get 3.5

``t:([]c: 30 40 25 20 4 4 4.5 4.5; c1: 10 20 5 25 5 4 3 3.5)``

 c c1 c2 c1>prev c2 OR prev c < prev c2 ? c1 prev c2 30 10 10 10>0 (True) OR No need of evaluation ? 10 40 20 20 20>10(True) OR No need of evaluation ? 20 25 5 20 5>20(False) OR 40<20(False) ? 20 20 25 25 25>20(True) OR No need of evaluation ? 25 4 5 5 5>25(False) OR 20<25(True) ? 5 4 4 4 4>5(False) OR 4<5(True) ? 4 4.5 3 4 3>4(False) OR 4<4(False) ? 4 4.5 3.5 4 3.5>4(False) OR 4.5<4(False) ? 4

``````update c2:fills ?[(c1>prev c1) or prev[c]<prev c1;c1;0N] from t
c   c1  c2
-----------
30  10  10
40  20  20
25  5   20
20  25  25
4   5   5
4   4   4
4.5 3   4
4.5 3.5 3.5``````

Valued Contributor

Yes in that case an accumulator is needed. One method you could choose would be to pass a table through to accumulate while also allowing you to look back to previous rows:

``````q)update c2:1_@[;`c2]{y[`c2]:enlist \$[(y[`c1][0]>last x[`c2]) or ((last x[`c])<last x[`c2]);y[`c1][0];last x`c2];x,y}/[enlist each {(1#0#x),x}update c2:0 from `c`c1#t] from t
c   c1  c2
----------
30  10  10
40  20  20
25  5   20
20  25  25
4   5   5
4   4   4
4.5 3   4
4.5 3.5 4``````
New Contributor III

I was trying something like below. I am not sure if it works in all scenarios.

I will validate your solution as well and revert soon.

Thanks much for your patience. KX community support and response is just excellent 👍

``````update c2: {?[((y>x)|(z<x));y;x]}\[0;c1;0^prev c] from t
c   c1  c2
----------
30  10  10
40  20  20
25  5   20
20  25  25
4   5   5
4   4   4
4.5 3   4
4.5 3.5 4``````

Valued Contributor

Yes that's much cleaner - as you only ever compute one value `c2` and only look back 1 step. this will do exactly as you need.

New Contributor III

Thanks for confirming. I checked both solutions and the result column matches exactly for my bigger data set.

New Contributor

From the Primary Accumulator list, select 0. From the First list, select Increment primary. From the Second list, select Use primary.Defines a custom accumulator operator. Accumulators are operators that maintain their state (e.g. totals, maximums, minimums,  click test   and related data) as documents . If you have access to Accumulator manager, you can access the Accumulator ... To locate a record, you can sort each column alphabetically, ...