Conditional Sum

Community Support
1

Hi All, wondering if you someone can help me out.  Can’t seem to get this, maybe its easy.

I have a table something like

flagval

1-100

1100

0100

0100

0-100

0100

1100

1-100

1100

0100

0100

1100

1100

I want to add to running total where flag is true and reduce running total where flag is false, but on the current side of the running total.  So something like

flagval     total

1-100     -100

1100      -200

0100      -100

0100      0

0-100     0

0100      0

1100      100

1-100     200

1100      300

0100      200

0100      100

1100       200 

1-100     300

Thanks

2

Hi Roni,

Something like this is probably what you want?

q)update too: sums ?[flag;1;-1]* val from tab

Many thanks,
Aidan

3

Thanks Aidan, I guess close but when I run in the example I get 

flagvaltoo

1-100-100

11000

0100-100

0100-200

0-100-100

0100-200

1100-100

1-100-200

1100-100

0100-200

0100-300

1100-200

1100-100

You can see on the second row, its not correct, it needs to check the side of the first row in order to be able to add to the total on the correct side

4

Hi,

You say you want to add to the running total when flag is true but in the second row that would add 100 to -100 and give 0. But you say you expect -200?

Regards,

Marcus 

5

Hi Roni,

Like Marcus, I’m not totally clear what logic you need, but whatever it is I suspect you need a custom scan function.  Something like:

update total:{x+y*z}[0;val;flag] from t

This won’t give you the result you posted, but it shows how you can use the current running sum value (x in this case) along with your value and flag columns (y and z) to generate the next running sum value.  Hope that helps!

All the best,

Matt Doherty

6

just flip the sign of ‘val’ by ‘flag’ run sums on the result:

update total:sums val*-1+flag+2 from t

7

Hi Roni, 

Following on from Matt’s suggestion that you use scan. Something like this might do what you need:

q)update total:{[x;f;v]if[not&not f;:x];$x=0;signum[v];s:signum x;s;::. abs x,v}[0;flag;val] from t

flag val total

---------------

1 -100 -100

1 100 -200

0 100 -100

0 100 0

0 -100 0

0 100 0

1 100 100

1 -100 200

1 100 300

0 100 200

0 100 100

1 100 200

1 100 300  

Regards,

Paul

8

> update total:sums val*-1+flag+2 from t

seems legit

9

Thank you all, Paul exactly what I was looking for, works. Thanks.