Advent of Code is now back for a limited time only! Complete as many challenges as you can to earn those badges you may have missed in December. Learn more about how to participate here!
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Remove records (randomness)

45179902
8 - Asteroid

I have a table like this:

 

RecordIDCodeAmount
1A-500
2A500
3B-500
4B300
5A500

 

The rule is that for a given Code if there are amounts that add up to 0, then I need to label the pairs for removal.

 

The tricky part is that there's no unique identifier and hence there's randomness. For example, since A on the first row is -500, A on the second row is 500, and A on the fifth row is also 500. Therefore, A on the first row adds either A on the second or the fifth row would result in 500. However, since there's only one -500, I would only need to remove one of the two 500s, not both. This means either row 2 or 5 -- both of which would be acceptable answers.

 

Please note that the answer I'm looking for should cover all scenarios, such as multiple -500 but one 500 and two -500 and two 500s (in which case all are marked for removal), etc. Only solutions that cover all possible scenarios would be marked accepted.

 

Outcome 1:

RecordIDCodeAmountLabel
1A-500Remove
2A500Remove
3B-500Keep
4B300Keep
5A500Keep

 

Outcome 2:

RecordIDCodeAmountLabel
1A-500Remove
2A500Keep
3B-500Keep
4B300Keep
5A500Remove

 

Any idea as to how I can achieve this?

2 REPLIES 2
RemcoV
8 - Asteroid

Hi @45179902,

 

I believe a Multi-Row Formula tool should to the trick.

I copied your input and the below configuration seems to provide the required outcome.

RemcoV_0-1685083988809.png

 

Would this work for you?

 

Regards,

Remco

Raj
16 - Nebula

Find the sol attached

Labels
Top Solution Authors