Remove duplicate sites that have different 'operators'

Hello, I have a large list of sites where there are multiple duplicates of the same 'Site Address'. I can't just use the 'Remove Duplicates' tile in ETL as there can be multiple panels on one site.
I'm looking to sort the sites using the 'Latest update' date field. Then I'd like to remove sites that have the same address but with a different 'Operator'. This would leave me with a list of sites that are In Charge.
Best Answers
-
If I'm understanding correctly, I'd do a group by tile by Site, find the MAX(Latest Updated) and then do an inner join back on Site and MAX(Latest Updated) = LatestUpdated to only get the most recent rows per site.
0 -
Can you use 2 criteria for your remove duplicates tile: 'Site Address' and 'Panel'?
If not, you can use the Rank & Window tile and choose 'Latest Update' as the order and set 'Site Address' as the partition - here again you can use multiple fields if needed.
Next use a filter tile to only keep rows with the rank value you want to keep, presumably those with a rank of 1.If I solved your problem, please select "yes" above
0
Answers
-
If I'm understanding correctly, I'd do a group by tile by Site, find the MAX(Latest Updated) and then do an inner join back on Site and MAX(Latest Updated) = LatestUpdated to only get the most recent rows per site.
0 -
Can you use 2 criteria for your remove duplicates tile: 'Site Address' and 'Panel'?
If not, you can use the Rank & Window tile and choose 'Latest Update' as the order and set 'Site Address' as the partition - here again you can use multiple fields if needed.
Next use a filter tile to only keep rows with the rank value you want to keep, presumably those with a rank of 1.If I solved your problem, please select "yes" above
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 657 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 775 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 81 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive