Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
Sorry, this didn't work. However I understood the direction.