cancel
Showing results for 
Search instead for 
Did you mean: 

Recursive Query

hoffmanroni
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 val2
A 1 0
A 2 0
A 3 B 0
A 4 0
A 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 val2
A 1 1
A 2 3
A 3 B 3
A 4 7
A 5 12


Have tried these

update val2:{[v;f;v2]
    $[f=`B;
        prev v2;
        (prev v2) + v               
    ]
    }'[val;flag;val2] by sym from t1


sym val flag val2
A 1 1
A 2 2
A 3 B 0
A 4 4
A 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 t1


sym val flag val2
A 1 1 2 3 4 5f
A 2 1 2 3 4 5f
A 3 B 0f
A 4 0f
A 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

rathore_ajay
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 t1
sym val flag val2
-----------------
A   1        1
A   2        3
A   3   B    3
A   4        7
A   5        12



-Ajay

another way

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

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

Thanks you Ajay, much appreciated

How about:

update val2:sums val*`B`?flag from t1

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?


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.
-Ajay

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

This saves having to do an if else check every loop