I have a file that has Date, Batch_Id and Active users. I need to keep the latest row in the file

Sbhatia
Sbhatia Member
in Magic ETL

I am trying to do this with magic ETL but I am not having much luck.

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    I'm assuming by the latest row you mean the one with the most recent date and not the last row in your dataset.

    Using Magic ETL - feed your dataset into an add constant tile, add a constant called "Join Column" with a value of 1, feed that into a group by tile and group by this new field selecting the Maximum date, then take this output and using a Join Tile do an inner join on your original dataset and the grouped dataset based on your Max date = date.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can use the group by tile and then use batch_id and choose Max for your aggregate. Put date and user_id in your select list.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    I'm assuming by the latest row you mean the one with the most recent date and not the last row in your dataset.

    Using Magic ETL - feed your dataset into an add constant tile, add a constant called "Join Column" with a value of 1, feed that into a group by tile and group by this new field selecting the Maximum date, then take this output and using a Join Tile do an inner join on your original dataset and the grouped dataset based on your Max date = date.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Sbhatia
    Sbhatia Member

    Thanks so much for your quick response, maybe I should have been slightly clearer in my question.
    My file has multiple duplicate rows with the same date, so for example 20/09/23 could appear 4 times.
    I would like to keep the row for each date that has the maximum batch_id.

  • GrantSmith
    GrantSmith Coach

    You'd do the same thing above except use the batch id instead of the date to get the latest batch ID records.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can use the group by tile and then use batch_id and choose Max for your aggregate. Put date and user_id in your select list.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Sbhatia
    Sbhatia Member

    Thanks both for your responses, you have been a great help.

Categories