Filtering options by group and date

[rahulshetty925]

Hi All,

Is there a better or quicker way to only show the highlighted values below and filter out the unhighlighted rows in an automated way

the condition to filter out depends on todays date - closest date to today() and is kept and other row is deleted.

Thanks for the help

ID	cate 1	cate 2	date	todays date
123	A	RR	11/4/2024	10/3/2024
123	A	RR	11/7/2024	10/3/2024
123	B	RR	11/4/2024	10/3/2024
123	B	RR	11/7/2024	10/3/2024
456	C	BD	11/4/2024	10/3/2024
456	C	BD	11/7/2024	10/3/2024
789	D	BZ	11/4/2024	10/3/2024
789	D	BZ	11/7/2024	10/3/2024

[CoG]

Here are two ways to accomplish this, both involve Summarizing to identify the closest date. If you only ever have 1 row per date for a given ID, cate1, & cate2, then you can use the Sort and Unique Tool, but for generalizability, the Summarize Tool works better.

[Qiu]

@rahulshetty925  @CoG 
Another less efficient way.

I choose use the sample tool to get one Date that is most closest to today (Distance).

Then join this date with original data stream to get all the date those are closest to today.

[acotta17]

Hi @rahulshetty925,

Another way to do this is to use the Sort Tool with the Tile Tool and then filter. Just sort your data (ID, cate 1, cate 2, date - ascending) and use the Tile Tool with Tile Method as Unique Value and Unique columns checked [ID, cate 1, cate 2] and then filter for Tile_SequenceNum = 1.

Note: If you have rows where date<today's date, just use a filter tool before sorting. 

Hope this helps!