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