Accessing previous calculated value from a column in current row

Community Support
,
1

https://learninghub.kx.com/forums/topic/accessing-previous-calculated-value-from-a-column-in-current-row

Hello q experts,

I have a table created like below.

t:([]c1: 1 2 3 4 5 6 7 8 9)

Below statement works fine as I am using previous value of column c1.

update c2:((prev(c1)+10) + c1)%2 from t

If I try to use previous value from column c2, then there is a error.

update c2:((prev(c2)+10) + c1)%2 from t

 

How can I use the previous calculated value of c2 in the current row calculation?

 

 

2

https://code.kx.com/q/ref/accumulators/#binary-values

q)select c1,c2:0{(x+10+y)%2}c1 from t 
c1 c2 
----------- 
1 5.5 
2 8.75 
3 10.875 
4 12.4375 
5 13.71875 
6 14.85938 
7 15.92969 
8 16.96484 
9 17.98242
3
Hello,
To modify the table in place, pass it by name (i.e. `t).
9. Queries q-sql - Q for Mortals (kx.com)
q)t:([]c1: 1 2 3 4 5 6 7 8 9) 
q)update c2:((prev(c1)+10) + c1)%2 from `t `t 
q)update c2:((prev(c2)+10) + c1)%2 from `t `t 
q)t 
c1 c2 
-------- 
1 2 
3 9.75 
4 10.75 
5 11.75 
6 12.75 
7 13.75 
8 14.75 
9 15.75
 
4

Thanks for the reply vkennedy.

Actually I don’t want to update the existing table at all. Sorry for confusing with update keyword. (But I learnt something new here Thanks).

Please consider below case. The select fails here.

t:([]c1: 1 2 3 4 5 6 7 8 9)select c1, c2:((prev(c2)+10) + c1)%2 from t

How can I use the previous calculated value of c2 in the current row calculation? For the first row in the table there is no prev(c2); so that could be considered as 0. From the second row, it should consider the previous calculated value of c2.

 

5

Hi,

Is this the result you are looking for?

q)select c2:(0^(prev(c2)+10) + c1)%2 from update c2:((0^prev(c1)+10) + c1)%2 from t 
c2 
----- 
0 
6.25 
9.75 
10.75 
11.75 
12.75 
13.75 
14.75 
15.75

In this case, you need to explicitly fill in the zero as the result of prev on the first item of a list is null.

Appendix A. Built-in Functions - Q for Mortals (kx.com)

6

Hi,

I have added the calculations below.

c1 c2 Calculation
1 5.5 =((0+10) + 1)/2 =((prev(c2)+10)+c1)/2
2 8.75 =((5.5+10) + 2)/2
3 10.875 =((8.75+10) + 3)/2
4 12.4375 =((10.875+10) + 4)/2
5 13.71875 =((12.4375+10) + 5)/2
6 14.859375 =((13.71875+10) + 6)/2
7 15.9296875 =((14.859375+10) + 7)/2
8 16.96484375 =((15.9296875+10) + 8)/2
c2 column values is the expected result.
7

Wow!! Thanks Jason & vkennedy.