Straight table - Data of last date and 2nd last date in one row

[A7R3]

Hi Experts,

I have a requirement to organize data by its dates in a single row in a straight table. 

This is how the raw data looks like.

Material	GroupColor	NameLatest	ControllerUpdate01	DateControllerUpdate
61212	1. Dark Red	LJ	5pcs ETA 30Apr	4/15/2021
61212	1. Dark Red	LJ	10pcs ETA 30Apr	4/18/2021
12185	1. Dark Red	LJ	demand upside	4/15/2021
12954	1. Dark Red	LJ	change to A/F	4/15/2021
12954	1. Dark Red	LJ	GR on Apr 18	4/18/2021
17057	1. Dark Red	LJ	expediting	4/15/2021
17057	1. Dark Red	LJ	Still expediting	4/18/2021

This is the desired outcome, where the Latest Update should be the data of the latest date.  So for 12185, it should be Apr 15, and for others, they should be Apr 18.

Material	GroupColor	NameLatest	Latest Update	Previous Update
61212	1. Dark Red	LJ	Apr18: 10pcs ETA 30Apr	Apr15: 5pcs ETA 30Apr
12185	1. Dark Red	LJ	Apr15: demand upside	-
12954	1. Dark Red	LJ	Apr18: GR on Apr 18	Apr15: change to A/F
17057	1. Dark Red	LJ	Apr18: Still expediting	Apr15: expediting

I can get it partially done, with Max(Date) and Max(Date,2), but the output looks like this. 

Material	GroupColor	NameLatest	IF(DateControllerUpdate='$(=Max(DateControllerUpdate))', Date(DateControllerUpdate,'MMMDD')&': '&ControllerUpdate01)	IF(DateControllerUpdate='$(=Max(DateControllerUpdate,2))', Date(DateControllerUpdate,'MMMDD')&': '&ControllerUpdate01)
61212	1. Dark Red	LJ	Apr18: 10pcs ETA 30Apr	-
61212	1. Dark Red	LJ	-	Apr15: 5pcs ETA 30Apr
12185	1. Dark Red	LJ	-	Apr15: demand upside
12954	1. Dark Red	LJ	Apr18: GR on Apr 18	-
12954	1. Dark Red	LJ	-	Apr15: change to A/F
17057	1. Dark Red	LJ	Apr18: Still expediting	-
17057	1. Dark Red	LJ	-	Apr15: expediting

How do I get the update of the same material number on the same row?

Appreciate any advice that you can give.

Thank you in advance.

[Digvijay_Singh]

try with firstsortedvalue function, that should help in this situation. You can find many good posts here in community.

[Digvijay_Singh · Accepted solution]

Something like this may be - 

[mwanders]

Hi,

If I have to do this then I will create two columns in the load script. I don't know if you have the possibilty to do this. Or maybe your data set is large.

One date you can create with the MAX function. The other date from a sorted table (max date on first row) and then use the PEEK function to the second row to get the second date. Both columns you can map to your existing table.  Your calculation will be also faster in your dashboard.

And even you can put the two dates in order in one column with the CONCAT funtion in your script.

[A7R3]

Thank you for the effort to work out the expression. It helps to confirm the solution quickly. Appreciate it.