topic Re: loading script - how to pick name based on lastest creation date and create as a field? in QlikView

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

jim_chan — Sat, 16 Nov 2024 21:10:25 GMT

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

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

passionate — Wed, 27 Mar 2019 04:11:32 GMT

Just use
Product_id&name as Productidlastname
In load script

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

passionate — Wed, 27 Mar 2019 04:19:23 GMT

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;

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

jonathandienst — Wed, 27 Mar 2019 05:10:35 GMT

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;

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

jim_chan — Wed, 27 Mar 2019 06:40:24 GMT

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