cancel
Showing results for 
Search instead for 
Did you mean: 

Iterating and calculating values based on previous

hoffmanroni
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)

val
0
0
0
0
0
0
0
0
0
0

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

val
1
2
3
4
5
6
7
8
9
10

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

val
1
1
1
1
2
3
4
5
6
7

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


TerryLynch
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 t
col col2
--------
8   9
1   11
9   21
5   27
4   32
6   39
6   46
1   48
8   57
5   63

You were missing a seed value (zero). 


Terry

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!


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!


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

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 0
1 8
10 1
12 9
22 5
28 4
33 6
40 6
47 1
49 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




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


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

You can shorten it a little bit

q)update col2:last each{1_x,1+y+x 0}\[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

-Ajay