Delete rows based on specific conditions

[barkat]

I have the below problem, if there is a row with action 'NEW' and directly below this row with the same id there is a 'CANCEL' .Then we need to remove  both rows ( new and cancel) .Can anyone hep suggest a logic in Alteryx for this? 

input data: 

Amount	ACTION	ID
400000	NEW	72
500000	NEW	12
500000	CANCEL	12
0	CORRECTION	12
20000	NEW	89
20000	CANCEL	89
10000	NEW	11
90000	NEW	20
12000	NEW	23
12000	CANCEL	23

output of what I want to achieve:

Amount	ACTION	ID
400000	NEW	72
0	CORRECTION	12
10000	NEW	11
90000	NEW	20

[Emil_Kos]

Hi @barkat,

I have prepared a workflow using multi-row formula tool:

I am comparing if the current row and the previous row meet specific requirements. After that, I am checking if the current row and the next row meet another need.

When it is done, I just need to filter out the data.

[mbarone]

Probably several ways to do this, but here's what I would do, as long as it will always be the cancel row beneath the row you want to delete:

Multi-row Formula to set a new field to 1 if it's "CANCEL" and the ID before it matches it's ID.

Another Multi-row Formula to set yet another new field to 1 if the row below it is 1.
Then Filter out all the 1s:

[mbarone]

Ha ha - it looks like @Emil_Kos and I were building out pretty much the same solution at the same time! 🙂

[Emil_Kos · Accepted solution]

Hi @mbarone,

Yes, that is true.

Initially, I wanted to use one tool with the formula below:

IIF([Row-1:ACTION] = 'NEW' AND [ACTION] = 'CANCEL' OR [ACTION] ='NEW' AND [Row+1:ACTION] = 'CANCEL', 'Y', null())

It didn't work so I moved to the nested if function to make it work:

IF [Row-1:ACTION] = 'NEW' AND [ACTION] = 'CANCEL' THEN 'Y' ELSEIF [ACTION] ='NEW' AND [Row+1:ACTION] = 'CANCEL' THEN 'Y' ELSE null() ENDIF

The output:

Mission accomplished 😀

[mbarone]

Nice!!  I knew it could be done, but was a bit too lazy this morning LOL!

[Emil_Kos]

Hi @mbarone,

I know you are aware of how to do that 😀

I was too busy and needed to finish something fast. I just needed to make it work 🤣

[mbarone]

I know how that is!  I think I have about 100 hours worth of "I need to know how to do that and will set some time aside one day to figure it out" projects LOL!

[barkat]

Thank you. That's vert helpful.