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: AmountACTIONID400000NEW72500000NEW12500000CANCEL120CORRECTION1220000NEW8920000CANCEL8910000NEW1190000NEW2012000NEW2312000CANCEL23 output of what I want to achieve: AmountACTIONID400000NEW720CORRECTION1210000NEW1190000NEW20

Delete rows based on specific conditions

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

Expression

Data Investigation

Accepted answers

Emil_Kos

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 😀

Multi Row v2.yxmd

All comments

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.

Multi Row.yxmd

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:

2021-04-16 07_45_19-alteryxpnv01 - Remote Desktop Connection.png

mbarone

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

Emil_Kos

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 😀

Multi Row v2.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280