Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Filtering for duplicate values only if one of those values is negative

NicC
7 - Meteor

Hey folks,

 

I've got tens of thousands of rows of transaction data, and due to the accounting system it comes from, there are pairs of data I need to remove. However, I'm struggling to specify that pairs of rows should only be deleted where one of them are negative.

 

I've tried the unique tool, but in some cases, there are 4 entries of $10 and 1 entry of -$10. Only 1 of the positive and 1 of the negative should be deleted. You can see an example below:

 

P.S. I just noticed there's a table tool I could have used. Hopefully my formatting works. X_X

 

E.g. scenario - want to remove the red
expense code              amount     absolute value    key (category + abs + account code)               count of absolute value
12345-67                    10             10                        Meal1091110-67                                               5

12345-67                    10             10                        Meal1091110-67                                               5

12345-67                    10             10                        Meal1091110-67                                               5

12345-67                    10             10                        Meal1091110-67                                               5
12345-67                    -10            10                        Meal1091110-67                                               5
12345-67                    43              43                       Meal4391110-67                                               1
12345-67                    40              40                       Auto4091110-67                                                1

12345-67                    100            100                     Airfare10091110-67                                           2
12345-67                    -100           100                     Airfare10091110-67                                           2

12345-67                    -90             90                       Meal9091110-67                                                5

4 REPLIES 4
AbhilashR
14 - Magnetar
14 - Magnetar

Hi @NicC, you could use the multi-row formula tool to identify the records that needs to be filtered. I have attached a prototype for you to build upon.

AbhilashR_0-1589483729353.png

Let us know if this isn't what you are looking for.

 

zajaccount
9 - Comet

Hi

 

maybe not the cleanest solution but I think I managed to do this using 2x multi row formulas.


Please let me know if this works for you

NicC
7 - Meteor

Thank you very much!! I still need to tweak it a little, but this got me 90% of the way there and will save me a ton of time!

NicC
7 - Meteor

Thanks for your response Abhilash! This got me nearly there. Ultimately, it needed the sort tool from the other response.

I appreciate your help!

Labels