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 LocationCurrencyUPPER/LOWERNameLastUpdateLondonGBPUPPERKen12/12/2016 8:34LondonGBPLOWERJIM12/12/2016 8:31LondonGBPUPPERKen12/10/2016 8:34LondonGBPLOWERJIM12/10/2016 8:31USAUSDLOWERNick12/11/2016 8:31USAUSDUPPERPete12/11/2016 9:31USAUSDUPPERPete11/10/2016 9:31FRNEURLOWERBarb12/12/2016 10:12ChinaHKDUPPERJon12/30/2016 23:45ChinaHKDLOWERJon12/30/2016 23:45ChinaHKDUPPERJon12/31/2016 23:45ChinaHKDLOWERJon12/31/2016 23:45IndiaINRUPPERCarl1/2/2017 14:23 Output Data LocationCurrencyUPPER/LOWERNameLastUpdateLondonGBPUPPERKen12/12/2016 8:34LondonGBPLOWERJIM12/12/2016 8:31USAUSDLOWERNick12/11/2016 8:31USAUSDUPPERPete12/11/2016 9:31FRNEURLOWERBarb12/12/2016 10:12ChinaHKDUPPERJon12/31/2016 23:45ChinaHKDLOWERJon12/31/2016 23:45IndiaINRUPPERCarl1/2/2017 14:23

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

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

Date Time

Best Practices

Transformation

Help

Dynamic Processing

Expression

Accepted answers

StellaBon

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

NMangera

@KamalChoudhary_in ,

the workflow would look something like...

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

Filter data to get most recent date based on filter to multiple column.yxmd

All comments

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

StellaBon

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

NMangera

@KamalChoudhary_in ,

the workflow would look something like...

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

Filter data to get most recent date based on filter to multiple column.yxmd

KamalChoudhary_in

Thanks for helping out @StellaBon

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335