cancel
Showing results for 
Search instead for 
Did you mean: 

Accumulators - Access additional list / column

RV
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

rocuinneagain
Valued Contributor
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

View solution in original post

RV
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

 

 

View solution in original post

9 REPLIES 9

rocuinneagain
Valued Contributor
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

 

RV
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)

 

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

 

 

rocuinneagain
Valued Contributor
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

 

RV
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)

 

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

 

rocuinneagain
Valued Contributor
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

RV
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

 

 

rocuinneagain
Valued Contributor
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.

RV
New Contributor III

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

binitafelicity
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, ...