Accessing previous calculated value from a column in current row

[RV]

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?

[vkennedy]

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

[RV]

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.

[vkennedy]

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)

[RV]

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.

[Jason_Fealy · Accepted solution]

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

[RV]

Wow!! Thanks Jason & vkennedy.