Looking for Specific Value

[knnwndlm]

Hi SME,

I have a Batch Macro that allocates the negative value into the positive value(s) within the same Tile.  What I want to create is an Iterative Macro inside the Batch Macro to **bleep** out those line items within the same tile that have the same value but opposite signs (see highlighted rows in the attached file).  In other words, I want to zero out those line items so that I won't have to allocate to the line items that contain the same values with the opposite sign.

Not sure if I can create an Iterative Macro inside a Batch Macro.  If so, not sure how to approach this.  Any suggestions?

Thanks,

kwl

[jrlindem]

Feels like this might be just a formula tool to flag the instances where this occurs and then just filtering them out?  -Jay

[knnwndlm]

@jrlindem - But how do I perform this within each tile so that if I have the same amount elsewhere, it won't be picked up?  I need to go through each row within the tile to see if there's another row that has the same value with the opposite sign.

[knnwndlm]

@jrlindem - Never mind.  Think I figured this out.  Thank you!

[jrlindem]

@knnwndlm Oh nice!  Did you get the embedded macro's working?  Or did you figure out the tiles + formulas? Curious what you worked out.  -Jay

[knnwndlm]

@jrlindem - Thought I got it with the tiles + formula + multi-row formula but came to realize that it wasn't working due to other attributes embedded in the process. The issue I have is that I could have rows of embedded between the pos/neg values that I try to zero out.  It would have been easier if I could like them all up and then use the multi-row formula to do this.

[jrlindem · Accepted solution]

Interesting.  What if you created a duplicate of the field and applied the absolute value ABS() of the negative values so that you can temporarily sort them according to number order within each tile, then do your multi-row formula against the original field (that still has the negative values).  You're only using the duplicate field with ABS() to create the sort order, then you abandon that later....

Your example data does not actually have any examples where there are other numbers in between the +/- sets, but I think this concept still works.  Just re-arrange your data so that the +/- values are next to each other, then use the multi-row formula.

-Jay

[knnwndlm]

@jrlindem - What is the best way to zero out those rows?  I could do a filter, but I want to see those rows zeroed out for auditing purposes.  I wasn't able to use the Multi-Row Formula tool to do this in one swoop.  Any thoughts?

[knnwndlm]

@jrlindem - I did it with two separate Multi-Row Formula. Guess it's not possible to do it with one.

[jrlindem]

Glad you got it figured out.  For your question, about the zero'd out rows, I would probably have recommended filtering, but since the filter tool splits it into [T]rue and [F]alse, you could bring the zero'd rows back into after you multi-row-formula'd to reunite them with the main rows.  But it appears you're good to go!

Happy Alteryx'ing and have a great rest of your week.  -Jay