2022.09.05 02:07 AM
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>x0^c <x;y;x]}\c1 from t
2022.09.06 01:38 AM
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
2022.09.06 02:04 AM
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
2022.09.05 02:35 AM
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
2022.09.05 04:41 AM
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
2022.09.05 07:05 AM
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
2022.09.05 10:20 PM
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
2022.09.06 01:38 AM
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
2022.09.06 02:04 AM
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
2022.09.06 02:21 AM
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.
2022.09.06 11:50 PM
Thanks for confirming. I checked both solutions and the result column matches exactly for my bigger data set.
2022.09.06 11:23 PM
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, ...
EMEA
Tel: +44 (0)28 3025 2242
AMERICAS
Tel: +1 (212) 447 6700
APAC
Tel: +61 (0)2 9236 5700
KX. All Rights Reserved.
KX and kdb+ are registered trademarks of KX Systems, Inc., a subsidiary of FD Technologies plc.