Hello all.
Please see attached. Outcome is as desired.
If the Ef amount is >0 and no other entry for the same account number then 1
if Ef mount <0 and no other entry for the same account then 0
if sum ef amount =0 then +1 for +ve number and -1 for the -ve number
if sum ef amount <>0 then +1 for +ve number and 0 for the -ve number
I used Multirow formula and bunch of either weird things to get to the result(column name=New Field), but when you look at the last Policy # ( J) it is should show -1 and +1 for ef amount =-49514 and 49514 and +1 for 24757, but it is not doing so. any help will be appricated. I would like to see if someone can show me how to solve all these in alteryx, I created like a union join on the same table one for +ve number and 1 for -ve number.
This took me a couple of attempts to see what you were trying to do. I've left my first attempt in the attached so you can see how I initially interpreted it if you're interested!
I think the reason you're not getting the result you want for policy J is that the matching value positive and negative rows aren't consecutive. Rather than a multi-row formula, I've used tile tools to match up values that should show +1/-1 (I think) then assigned all the values in a single formula. The tile tools are important if there are repeating values.