showing record with max date

meadow_ryan

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

ST_Superman

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

meadow_ryan

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

ST_Superman

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.

 

 

 

meadow_ryan

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?

meadow_ryan

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

Canioagain

@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

CarolineRogg1

This was very helpful for me!!