cancel
Showing results for
Did you mean:

## Recursive Query

New Contributor III
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 (`sym`val`flag`val2)!(`A;1f;`;0f);`t1 insert enlist (`sym`val`flag`val2)!(`A;2f;`;0f);`t1 insert enlist (`sym`val`flag`val2)!(`A;3f;`B;0f);`t1 insert enlist (`sym`val`flag`val2)!(`A;4f;`;0f);`t1 insert enlist (`sym`val`flag`val2)!(`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!

8 REPLIES 8
New Contributor

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`

-Ajay
New Contributor
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`
-Ajay
New Contributor
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.
-Ajay
New Contributor III
Thanks you Ajay, much appreciated
New Contributor
`update val2:sums @[val;where flag=`B;:;0.] by sym from t1`