cancel
Showing results for 
Search instead for 
Did you mean: 

Accessing previous calculated value from a column in current row

RV
New Contributor II

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?

 

 

1 ACCEPTED SOLUTION

Jason_Fealy
New Contributor III

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

View solution in original post

6 REPLIES 6

vkennedy
New Contributor II
New Contributor II
Hello,
 
To modify the table in place, pass it by name (i.e. `t).
 
 

 

 

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
New Contributor II

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
New Contributor II
New Contributor II

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
New Contributor II

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
New Contributor III

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
New Contributor II

Wow!! Thanks Jason & vkennedy.