cancel
Showing results for
Did you mean:

## Iterating and calculating values based on previous

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

10 REPLIES 10
User
Not applicable
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`

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

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

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

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

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

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

New Contributor II
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]#(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

New Contributor III
ok I think thats a bit too messy.  I will rethink this abit.  Thanks for all the help Terry much appreciated.
New Contributor
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`

-Ajay