cancel
Showing results for 
Search instead for 
Did you mean: 

Applying moving function on previous records of a table

vivek_shende
New Contributor
I want to apply a moving function on prev records of a column in a table. How can I achieve it in Kdb?

q) tab:([] n:0.2 0.4 0.1 0.6 -0.5 -0.3)
q) tab
n
---
0.2
0.4 
0.1
0.6 
-0.5
-0.3

Expected output:
n     op
-----------
0.2   10
0.4   14
0.1   15.4
0.6   24.64
-0.5  12.32
-0.3  8.62

Here, the function applied is (prev op)*(1+n), the initial value for first record starts at default value 10.
7 REPLIES 7

rathore_ajay
New Contributor

q)update op:{x*1+y}\[10.,1_n] from tab
n    op   
----------
0.2  10   
0.4  14   
0.1  15.4 
0.6  24.64
-0.5 12.32
-0.3 8.624


-Ajay

Hey Ajay, 

Thanks for the solution! 

I completely forgot about nulls when applying this solution. I want to treat nulls as if they don't exist and continue calculations on non-null values. I tried using various combinations of fills, except 0n, but couldn't figure out how to handle it. 
Considering same example and introducing some null values in it, I want to achieve the following:

q) tab:([] n:0n 0.2 0.4 0n 0n 0.1 0n 0.6 -0.5 -0.3)
q) tab
n
---
0n
0.2
0.4
0n
0n
0.1
0n
0.6
-0.5
-0.3

Expected output:
n             op
--------------------
0n 0n
0.2 10
0.4 14
0n 0n
0n 0n
0.1 15.4
0n 0n
0.6         24.64
-0.5        12.32
-0.3        8.624

ajay
New Contributor II
You can just add a where clause

q)update op:{x*1+y}\[10.,1_n] from tab where not null n
n    op  
----------
         
0.2  10  
0.4  14  
         
         
0.1  15.4
         
0.6  24.64
-0.5 12.32
-0.3 8.624

Thanks for the reply Ajay! but I was looking for something that can retain nulls instead of removing those.

An alternative approach using the built-in prds function

q)update op:10*prds 1+n*i>0 from tab
n    op
----------
0.2  10
0.4  14
0.1  15.4
0.6  24.64
-0.5 12.32
-0.3 8.624

There's also a specific shortcut for operations such as this, though it's not ideally suited to this example:

q)update op:10(1+n*i>0)\0&i from tab
n    op
----------
0.2  10
0.4  14
0.1  15.4
0.6  24.64
-0.5 12.32
-0.3 8.624


Terry

Thanks Terry, it looks simpler. Do you think it can be modified to return null if n is null and continue calculations for non-null as if nulls don't exist?

Ajays approach does precisely this, have you actually tested it?

Alternatively with a little bit of effort you should be able to take either of our suggestions, figure out how they work and then modify them to achieve what you need (use fill ^ within the iterations for example).

Terry