flag cancelled transcations

[Jocelynupup]

Hi, 

I have a big dataset showing the transaction details. Based on it, I created the table 1 as an example and result as my expected outcome.

My problem is: 

I want to flag cancelled transactions. I was supposed to use join tool to match buy and cancel. However, some cashiers may make a mistake. They cancel the wrong transaction and do the buy again. Which means their identifiers are exactly same, same date, same TransID. So, I am trying to use their time to find those cancelled transactions. Maybe multi-row formula tool? But I don't know how to write the expression. The logic is after the sorting (time, TransID), if there is a cancel transaction, [TransID]Row -1=[TransID]Row,  [Amount]Row -1=[Amount]Row, then flag [cancelled]Row -1 and [cancelled]Row as "x". I wonder whether we can do it in Alteryx.

table 1:

TransID	Date	time	transaction	Amount
2	11/1/2023	9:00:00 AM	buy	25
4	11/1/2023	10:30:00 AM	buy	100
1	11/1/2023	10:00:00 AM	buy	10
1	11/1/2023	10:01:00 AM	cancel	-10
4	11/1/2023	10:31:00 AM	cancel	-100
5	11/1/2023	11:00:00 AM	inquiry	0
1	11/1/2023	10:02:00 AM	buy	10
3	11/1/2023	11:00:00 AM	buy	30

result:

TransID	Date	time	transaction	Amount	cancelled
1	11/1/2023	10:00:00 AM	buy	10	x
1	11/1/2023	10:01:00 AM	cancel	-10	x
1	11/1/2023	10:02:00 AM	buy	10
2	11/1/2023	9:00:00 AM	buy	25
3	11/1/2023	11:00:00 AM	buy	30
4	11/1/2023	10:30:00 AM	buy	100	x
4	11/1/2023	10:31:00 AM	cancel	-100	x
5	11/1/2023	11:00:00 AM	inquiry	0

Thanks,

Jocelyn

[gautiergodard · Accepted solution]

Hey @Jocelynupup 

Would this work for you?

[TimN]

Similar solution

[putu_w_erata]

A different solution using Summarize

[Jocelynupup]

Thank you! It works! That's my first time to use multi-row tool. It's super helpful for me to understand how a correct expression looks like.