Filter data to get most recent date based on filter to multiple columns

[KamalChoudhary_in]

Hi All,

I want to get the LastUpdate based on the Location , Currency, UPPER/LOWER ,when we filter London , and select upper and lower take most recent date, any suggestion is much  appreciated.

Input Data

Location	Currency	UPPER/LOWER	Name	LastUpdate
London	GBP	UPPER	Ken	12/12/2016 8:34
London	GBP	LOWER	JIM	12/12/2016 8:31
London	GBP	UPPER	Ken	12/10/2016 8:34
London	GBP	LOWER	JIM	12/10/2016 8:31
USA	USD	LOWER	Nick	12/11/2016 8:31
USA	USD	UPPER	Pete	12/11/2016 9:31
USA	USD	UPPER	Pete	11/10/2016 9:31
FRN	EUR	LOWER	Barb	12/12/2016 10:12
China	HKD	UPPER	Jon	12/30/2016 23:45
China	HKD	LOWER	Jon	12/30/2016 23:45
China	HKD	UPPER	Jon	12/31/2016 23:45
China	HKD	LOWER	Jon	12/31/2016 23:45
India	INR	UPPER	Carl	1/2/2017 14:23

Output Data 

Location	Currency	UPPER/LOWER	Name	LastUpdate
London	GBP	UPPER	Ken	12/12/2016 8:34
London	GBP	LOWER	JIM	12/12/2016 8:31
USA	USD	LOWER	Nick	12/11/2016 8:31
USA	USD	UPPER	Pete	12/11/2016 9:31
FRN	EUR	LOWER	Barb	12/12/2016 10:12
China	HKD	UPPER	Jon	12/31/2016 23:45
China	HKD	LOWER	Jon	12/31/2016 23:45
India	INR	UPPER	Carl	1/2/2017 14:23

[StellaBon]

@KamalChoudhary_in Use a Summarize tool and group by Location, Currency, U/L. This eliminates duplicates. Then in the same tool add LastUpdate but instead of "grouping by" you select Max to get the most recent date. Hope this helps. EDIT: Join back to main dataset if you need additional columns. Let me know how this goes!

[KamalChoudhary_in]

Hi Stella,

Thank you very much for response , what if we need to find latest date for each month based on previous requirement  in last update column 

Input Dat 

Location	Currency	UPPER/LOWER	Name	LastUpdate
London	GBP	UPPER	Ken	12/12/2016 8:34
London	GBP	LOWER	JIM	12/12/2016 8:31
London	GBP	UPPER	Ken	11/12/2016 8:34
London	GBP	LOWER	JIM	11/12/2016 8:31
London	GBP	UPPER	Ken	12/10/2016 8:34
London	GBP	LOWER	JIM	12/10/2016 8:31
USA	USD	LOWER	Nick	12/11/2016 8:31
USA	USD	UPPER	Pete	12/11/2016 9:31
USA	USD	UPPER	Pete	11/10/2016 9:31
FRN	EUR	LOWER	Barb	12/12/2016 10:12
China	HKD	UPPER	Jon	12/30/2016 23:45
China	HKD	LOWER	Jon	12/30/2016 23:45
China	HKD	UPPER	Jon	12/31/2016 23:45
China	HKD	LOWER	Jon	12/31/2016 23:45
India	INR	UPPER	Carl	1/2/2017 14:23

Output data 

Location	Currency	UPPER/LOWER	Name	LastUpdate
London	GBP	UPPER	Ken	12/12/2016 8:34
London	GBP	LOWER	JIM	12/12/2016 8:31
London	GBP	UPPER	Ken	11/12/2016 8:34
London	GBP	LOWER	JIM	11/12/2016 8:31
USA	USD	LOWER	Nick	12/11/2016 8:31
USA	USD	UPPER	Pete	12/11/2016 9:31
FRN	EUR	LOWER	Barb	12/12/2016 10:12
China	HKD	UPPER	Jon	12/31/2016 23:45
China	HKD	LOWER	Jon	12/31/2016 23:45
India	INR	UPPER	Carl	1/2/2017 14:23

[StellaBon]

@KamalChoudhary_in You use a formula tool, and the DateTimeMonth() and DateTimeYear() functions to create  month and year columns, which allows you to "group by" year and month, and get the "Max" entry for each month. Play around with it! Let me know how it goes. If this has helped please mark as "Solved". 

[KamalChoudhary_in]

I have parsed month column and added to summarize tool as group by it worked , Kindly correct me if my approach is wrong 

[StellaBon · Accepted solution]

@KamalChoudhary_in Great! If you're happy with the outcome, then it worked. Glad to help. 😊

[NMangera · Accepted solution]

@KamalChoudhary_in ,

the workflow would look something like...

[KamalChoudhary_in]

Thanks for helping out @StellaBon