Applying moving function on previous records of a table

[vivek_shende]

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.

[rathore_ajay]

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

[vivek_shende]

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]

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

[vivek_shende]

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

[TerryLynch]

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

[vivek_shende]

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?

[TerryLynch]

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