Removing Duplicates and making sure only the one with the most recent move in stays

I am working on an ETL where I am transforming some unit rental records. I am trying to only include the most recent rental for the unit not historical data but the input dataset includes historical data. When using the remove duplicates tile I do not have control over which ones are removed or added as they arent necassarily in historical order in the input data source.
How can I make it so that only the most recent rental for each unit is left after remove duplicate of the unit column is done.
Date column is named MoveDate and unit column is named unit. What I want is to remove duplicates in the unit column leaving only the entry with the most recent date in MoveDate
Best Answer
-
If you use a Rank & Window tile in your ETL you can apply the DENSE RANK function to rank on MoveDate descending and partition by the Unit Id. This should give you a Rank of 1 for the most recent date and then you can add a filter to filter for Rank = 1
1
Answers
-
If you use a Rank & Window tile in your ETL you can apply the DENSE RANK function to rank on MoveDate descending and partition by the Unit Id. This should give you a Rank of 1 for the most recent date and then you can add a filter to filter for Rank = 1
1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 308 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 660 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- 780 Automate
- 191 Apps
- 482 APIs & Domo Developer
- 84 Workflows
- 23 Code Engine
- 41 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 2 AI Projects and Models
- 18 Jupyter Workspaces
- 413 Distribute
- 121 Domo Everywhere
- 281 Scheduled Reports
- 11 Software Integrations
- 145 Manage
- 141 Governance & Security
- 8 Domo Community Gallery
- 49 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive