showing record with max date

meadow_ryan
meadow_ryan Member
in Magic ETL

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

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • meadow_ryan
    meadow_ryan Member

    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

  • meadow_ryan
    meadow_ryan Member

    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
    meadow_ryan Member

    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
    Canioagain Contributor

    @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
    CarolineRogg1 Member

    This was very helpful for me!!

Categories