Applying moving function on previous records of a table

Community Support
1

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.

2

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

3

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

0n0n

0.210

0.414

0n0n

0n0n

0.115.4

0n0n

0.6        24.64

-0.5       12.32

-0.3       8.624

4

An alternative approach using the built-in prds function

q)update op:10prds 1+ni>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

5

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

6

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

7

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?

8

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