Iterating and calculating values based on previous

Community Support
1

Hi All,

I know I’ve done this before and seem to have gotten stuck again.  Wondering if someone can help me out. I want to calculate a columnar value based on its previous value at some index before it.

So if I have table t

t:([]val: 10#0f)val0000000000

and I want to add 1 to the value also add the prev value, this doenst work but tried

update val:{x+y+1}\[val;prev val] from t

result would be

val12345678910

and would like to be able to have the prev value based on some index, so for example something like

update val:{x+y+1}\[val;5 xprev val] from t

val1111234567

Hope that makes sense.  Basically iterating through a column and calculating the new values based on some previous index.

Thanks!

2

The first

q)update val:sums val+1 from t

The second question

q)update val:1+ sums val + 5 xprev (val + 1) from t

3

sums works nicely in this case as Callum showed, in the general case you’re probably thinking of

q)update col2:{x+y+1}\[0;col] from tcol col2--------8 91 119 215 274 326 396 461 488 575 63

You were missing a seed value (zero). 

Terry

4

Thanks Callum, Terry yes I was looking more for general case because what I posted was a simlification just as an example.  But how can I get the xprev value of col in this case?

update col2:{x+y+1}\[0;col] from t

what does the 0 represent here? 

Thanks!

5

Zero is the seed value. The logic is

prevFunctionResult + currentColumnEntry + 1

but for the first iteration there is no prevFunctionResult so you can set to a value of your choosing - I assumed you would want zero. This would seed it with 1000:

update col2:{x+y+1}\[1000;col] from t

I’m not 100% clear what you’re looking for with the xprev…are you looking for:

prevFunctionResult + columnEntryFiveRowsBefore + 1

? If so then you can do

update col2:{x+y+1}\[0;0^5 xprev col] from t

But this is inconsistent with the example posted originally. I’m not sure if your original example is logically consistent with an accumulator!

6

I think my original example wasn’t right.. 
So when I do this

update col2:{x+y+1}\[0;0^1 xprev col] from t

shouldn’t x=y? I dont see that

7

Not necessarily. 

Use 0N! to see the intermediary values of x and y

q)update col2:{0N!(x;y);x+y+1}\[0;0^1 xprev col] from t;0 01 810 112 922 528 433 640 647 149 8

8

Thanks Terry,

so what am really looking for is prevFuncResult5IterationsAgo  or some x number of iterations ago.  So I see seeding the value with 0 works for getting prevFuncResult  but I can’t seem to get anything before the prev result.

If I use 

q)update col2:{x+y+1}\[0;0^1 xprev col] from t;

then value of y here is the value xprev value of col, not the xprev value of the func result, while x here is the prev value of the func result.  So I guess question is it possible to get the funcResult at some x iteration ago?  Or am I missing something and do you think i should go back to drawing board a bit and rethink?

Thanks so much

9

Offhand, the only way I can think to do that is to keep a list of say the last 5 iterations. Not clean, but I think it works:

q)t:(col:8 1 9 5 4 6 6 1 8 5)

/current column value plus 5th previous function output plus one

q)update col2:last each {neg#(x#0),y,1+y[0]+z}[5][(),0;col] from t

col col2

--------

8   9

1   2

9   10

5   6

4   5

6   16

6   9

1   12

8   15

5   11

10

ok I think thats a bit too messy.  I will rethink this abit.  Thanks for all the help Terry much appreciated.

11

You can shorten it a little bit

q)update col2:last each{1_x,1+y+x 0}\[5#0;col] from tcol col2--------8 91 29 105 64 56 166 91 128 155 11