Can I filter by latest date?

damen
damen Contributor
in Magic ETL

Hi all,

I am working in magic ETL and I have a dataset that gets appended each month. Most of the data is useless for what I am trying to use, except for the latest month's set of data.

Is there a tile in Magic ETL that lets me filter out by latest date?

I believe I could use a rank and order but am trying to not use multiple tiles if necessary. Is this possible using just the filter tile?

Thanks

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You are going to need more than just the filter tile to do this. Here is bare minimum of what it would look like:

    image.png

    Add a Constants Tile that you will use for the join tile

    image.png

    Add a Group By Tile that gets the latest date

    image.png

    Add a Join tile that joins on that JoinKey constant you created earlier so that the LatestDate field is now repeated for every row.

    image.png

    Use the Filter tile to filter where the date equals the LatestDate field

    image.png

    This will filter down your data to the latest month

    **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.
  • nmizzell
    nmizzell Contributor
    Answer ✓

    @damen, This can be accomplished with the rank and filter tiles.

    Simply rank the dataset by date descending and keep only rank #1 in your filter tile.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You are going to need more than just the filter tile to do this. Here is bare minimum of what it would look like:

    image.png

    Add a Constants Tile that you will use for the join tile

    image.png

    Add a Group By Tile that gets the latest date

    image.png

    Add a Join tile that joins on that JoinKey constant you created earlier so that the LatestDate field is now repeated for every row.

    image.png

    Use the Filter tile to filter where the date equals the LatestDate field

    image.png

    This will filter down your data to the latest month

    **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.
  • nmizzell
    nmizzell Contributor
    Answer ✓

    @damen, This can be accomplished with the rank and filter tiles.

    Simply rank the dataset by date descending and keep only rank #1 in your filter tile.

Categories