showing record with max date
Hello I have a dataset which pulls record numbers(gn_id) and its pulling some duplicates and it looks like if it that gn_id has a duplicate I want to pull the one with the most recent or max date but am unsure on how to accomplish this, I have attached a screenshot of what I'm looking at as on this example would want to show the row with dispatch date of 6/4
thanks
Comments
-
You can filter the duplicates out using either a MySQL, or magic ETL dataflow. Let me know your preference and I can try to show you the steps.
If you want to keep the duplicate records in your dataset but only show one row on this card, try changing the agregation for your date column to be MAX
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
probably would need to do it through ETL, as doing it on the card I could have a bunch of gn_id numbers which have mulitple records and would just want to pull the one with the max dispatchdate
0 -
There may be other ways to do this, but I would start by ranking the dates and then filtering the data to only include the max date for each ID.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman5 -
so I added what you had suggested and my though was it would now in your example show the row for gn_id 1,111,111 with the max dispatchdate in this case being 5/28/19, correct?
1 -
attached is the way I currently have it, I have an inner join between loads and dispatch, loads contains gn_id, dispatch contains the dispatchdate
1 -
@ST_-Superman-_ An older post but VERY helpful. I forgot all about rank. Was about to do this in SQL.
Great pictures to follow along to
TY
0 -
This was very helpful for me!!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive