Recursive Query

Community Support
1

Hello qExperts, have another quick question if someone can help,

I am trying to have a recursive query where the row value is based on the preceding value in a recursive manner up to that point.  So for example

t1:([]sym:$();val:“F”$();flag:$();val2:"F"$());t1 insert enlist (symvalflagval2)!(A;1f;;0f);t1 insert enlist (symvalflagval2)!(A;2f;;0f);t1 insert enlist (symvalflagval2)!(A;3f;B;0f);t1 insert enlist (symvalflagval2)!(A;4f;;0f);t1 insert enlist (symvalflagval2)!(A;5f;;0f);sym val flag val2A 1 0A 2 0A 3 B 0A 4 0A 5 0`

Here what I want to do is update val2 recursively based on itself and a different column.  So ie if flag=, then val2 = val+(prev val2), else if flag=B then val2=prev val2

sym val flag val2A 1 1A 2 3A 3 B 3A 4 7A 5 12

Have tried these

update val2:{[v;f;v2] $[f=B; prev v2; (prev v2) + v ] }'[val;flag;val2] by sym from t1sym val flag val2A 1 1A 2 2A 3 B 0A 4 4A 5 5`

here with each, (prev val2) is not recursive so I can’t get the updated val2 from the previous iteration

update val2:{[v;f;v2] $[f=B; prev v2; v2 + v ] }[val;flag;val2] by sym from t1sym val flag val2A 1 1 2 3 4 5fA 2 1 2 3 4 5fA 3 B 0fA 4 0fA 5 0f`

here with iterate I get the whole val2 column and not the previously calculated val2

Hope that makes sense, I’m sure I’m missing something here but if you have any ideas would be greatly appreciated

Thanks!

2

If I understand your required output, you can use a simple scan 

q)update val2:{$[z=B;x;y+x]}[0;val;flag] by sym from t1sym val flag val2-----------------A 1 1A 2 3A 3 B 3A 4 7A 5 12`

3

another way

q)update val2:sums?[flag=B;0;val] by sym from t1sym val flag val2-----------------A 1 1A 2 3A 3 B 3A 4 7A 5 12`

4

Thanks Ajay, sorry I think I got turned around and confused myself.  I think I have it now and your post helped.  Quick question though it looks like the x arg you’re setting to 0 but how come it is the actual value of val2 during the iteration?

5

update val2:sums @[val;where flag=B;:;0.] by sym from t1`

This saves having to do an if else check every loop

6

Well, 0 is just setting the seed, and in each iteration either it is using the unmodified value from previous iteration or adding val to it. It is essentially folding the lambda onto y and z.

7

Not sure who is moderating this group these days, but noticed lately that replies are getting delayed sometimes by 2 to 3 hours and seems to appear out of order.

8

Thanks you Ajay, much appreciated

9

How about:

update val2:sums val*B?flag from t1