loading script - how to pick name based on lastest creation date and create as a field?

[jim_chan]

Hi guys, 

i have a question, and i do not know how to achieve it in loading script. i have created a sample qvw. and a screenshot.

My request:

user would like to create a new field - product_id & name(based on latest create_date).  user wants the productID concantenate with a name of last creation date. 

Rgds

Jim

[passionate]

Just use
Product_id&name as Productidlastname
In load script

[passionate]

If you have multiple date entry then.

Load Productid,
Name,
Creationdate,
Productid &Name as Productidlastname;
Load
Productid,
Name,
Max(creationdate) as creationdate
Resident table
Group by name, productid;

[jonathandienst]

First change the date format at the top of your script so that your dates are properly recognised:

SET DateFormat='D/M/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

Then add this after the inline load:

Join(table1)
LOAD product_id,
	product_id & FirstSortedValue(name, -create_date) as newField
Resident table1
Group By product_id;

[jim_chan]

Dear Jon, 

YES!! Thanks! i didnt know firstsortedvalue can be used at the back, most of the time, i used it at front end set analysis!

Thanks again!

Rgds

Jim