flag cancelled transcations
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Jocelynupup
Would this work for you?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
![](/skins/images/1A7F54316481E10DBCA4A87A32E06CC6/responsive_peak/images/icon_anonymous_message.png)