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

  • 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

  • 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.

    1.png

     

    2.png

     

    3.png

     

  • 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?

  • 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