Removing Offsetting Entries from a Data Set

[camilleallen1]

Hello,

I am trying to remove offsetting entries from a dataset in Alteryx. Where one amount is a positive entry (a debit) and the other amount which offsets it is a negative (a credit). Is there a quick way to identify and then remove these entries? 

[echuong1 · Accepted solution]

If your values will completely cancel each other out, the easiest way is to use a Summarize tool to get the total amount. From there, you can use a Join to add the Net Amount back to your original dataset. If the net amount is 0 the records cancel each other out (F output). If the records are not canceled out, they will have a net amount greater than 0 (T output).

If your values do not completely cancel each other out (say there are 3 lines and 2 cancel but 1 doesn't), you can try using a multi-row. Let me know if that's the case, and I can mock something up.

Hope this helps!

[camilleallen1]

Thank you so much!! This was super helpful and such a quick reply!

[jwardin]

Can you please post an example of a multi row where the values do not completely offset? Thank you. 

[andrewmcateer]

Hi @echuong1 , similar to @jwardin, a multi-row example would be very useful - my values will offset however there are some instances where there are 3 of the same values. e.g. -10, -10, 10. The goal would be to retain one -10 row and remove the cancelling ones.