Replace values by the most recent value

[GreatGreekYogurt]

Hi Everyone! 

I'm having trouble replacing values with the most recent value. To be more clear, my goal is to rename all products with the same ID number to the name used by the most recent date.

I have tried with ApplyMap and Replace, however unsuccessful. 

Table1:

LOAD * INLINE [

Date|Product ID|Product

01/05/1982|1|Datsun

01/20/1983|1|Nissan

01/08/2005|2|GM Daewoo

01/26/2009|2|Chevrolet
01/05/1996|3|BackRub

01/03/1997|3|Google

] (delimiter is '|');

First attempt:

Replace(Date,  FirstSortedValue(Product,Date),  FirstSortedValue(Product,-Date))

Result: Invalid expression. Can replace be combined together with FirstSortedValue? 

Second attempt:

MappingTable:

Mapping LOAD

[Product ID],

FirstSortedValue(Product, -Date) as Product

resident Table1;

FinalTable:

Load

Date,

[Product ID],

ApplyMap('MappingTable',Product) as Product

resident Table1;

DROP TABLE Table1;


Can someone please explain, what am I doing wrong here?

I would appreciate it a lot!

Thank you!

[Taoufiq_Zarra]

if I understood, may be :

SET DateFormat='MM/DD/YYYY';
Table1:

LOAD * INLINE [

Date|Product ID|Product
01/05/1982|1|Datsun
01/20/1983|1|Nissan
01/08/2005|2|GM Daewoo
01/26/2009|2|Chevrolet
01/05/1996|3|BackRub
01/03/1997|3|Google

] (delimiter is '|');

output :

load [Product ID],FirstSortedValue(Product,-Date(Date)) as Product resident Table1 group by [Product ID];
left join load * resident Table1;

drop table Table1;

[GreatGreekYogurt]

Sorry, this didn't work. However I understood the direction.