Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.
firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv
group by Customer;
LOAD Emp, firstsortedvalue(Dept,-Effective_dt) as Dept, firstsortedvalue(credit,-Effective_dt) as credit, date(max(Effective_dt)) as Effective_dt Group by Emp; LOAD * INLINE [ Emp, Dept, credit, Effective_dt A, 10A, 10, 1/1/2015 A, 12A, 200, 1/12/2014 A, 9B, 300, 1/11/2014 B, 10A, 90, 2/10/1999 B, 9B, 80, 1/9/2014 C, 9B, 25, 1/1/2014 ];
Please note we use "-" as sort order because we want the newest values