Can I filter by latest date?

damen

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

MarkSnodgrass

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

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

Add a Group By Tile that gets the latest date

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

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

This will filter down your data to the latest month

nmizzell

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

MarkSnodgrass

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

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

Add a Group By Tile that gets the latest date

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

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

This will filter down your data to the latest month

nmizzell

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