Filter Specific Rows from Data
- 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
Hello,
I'm hoping someone could assist in the best way to filter specific rows from a data set that aren't unique. Essentially I only want the last row that appears in the data set for the transaction for each unique line number. Sometimes the data has only one row and other times multiple rows.
Solved! Go to Solution.
- Labels:
- Fuzzy Match
- Tips and Tricks
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @TaxTitan, Looking at your data, it looks like Transaction and Line number is the combination for which you want last records.
1. You can first rank your data using Multirow formula - You will name the column Rank, group by Transaction and Line number and in formula write - Row-1: Rank +1.
2. Once you have rank, use a summarize tool, group by Transaction and Line number and find max Rank.
3. Use a join tool and join on Transaction and Line number and Rank.
This way your last records will get joined and you will get the desired output. Hope this helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Does this work? Instead of adding another grouping, I have used the existing "Line Number" field to find the last row each "group".
However, I am not sure why the order has changed after applying the Sample Tool.🤔
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much this works for my data! I was thinking of using the summarize tool as well and using last. But I wasn't sure which would be better.
![](/skins/images/9AF25434D4F136B28264A96CA92E8A93/responsive_peak/images/icon_anonymous_message.png)