topic Re: find firstsortedvalue (or minstring) from table per customerid in App Development

find firstsortedvalue (or minstring) from table per customerid

curiousfellow — Wed, 14 Feb 2024 15:55:58 GMT

I have a table containing customerid, valuefield and a sortfield that contains a string

Now I want per customerid the valuefield where the sortfield has the lowest value , seems simple but I get an error

this is my script:

table:

load customerid,

firstsortedvalue(valuefield,sortfield)

resident table 2

table2 is sorted by customerid and sortfield

Please hepl me with this, I struggle for several hours now but do not see what I am doing wrong.

Thanks in advande

Re: find firstsortedvalue (or minstring) from table per customerid

brunobertels — Wed, 14 Feb 2024 16:37:54 GMT

In script you nedd a group by clause when using firstsortedvalue

So :

load customerid,

firstsortedvalue(valuefield,sortfield)

resident table 2 group by customerid ;

Re: find firstsortedvalue (or minstring) from table per customerid

curiousfellow — Thu, 15 Feb 2024 08:14:14 GMT

Unfortunately it still does not work. I get a null value where I expect the value of the lowest year.

Please have a look at attatched file

Re: find firstsortedvalue (or minstring) from table per customerid

Antoine04 — Thu, 15 Feb 2024 08:35:26 GMT

Hello,

I am not able to open the .qvw you shared.

Could you please share an example of the DATA ? And the expected result ?

Then I can try on my own.

Regards

Re: find firstsortedvalue (or minstring) from table per customerid

BrunPierre — Thu, 15 Feb 2024 08:53:57 GMT

Hi @curiousfellow how about as below?

FirstValue(start_item) as first_value

FirstSortedValue(DISTINCT start_item,SubField(ordered_key,'_',2)) as first_value

Re: find firstsortedvalue (or minstring) from table per customerid

curiousfellow — Thu, 15 Feb 2024 09:06:08 GMT

The result is wrong. The result is the start_item of 2013 while it should be the startitem of 2012.

start_item of 2013 is lower than the start_item of 2012.

Seems to work when I first sort the table, strange what is the function of sort_weight then ?

I will try this in my original QVW and let you know

Re: find firstsortedvalue (or minstring) from table per customerid

curiousfellow — Thu, 15 Feb 2024 08:59:20 GMT

table:
load * inline [
company,start_item,employeeid, year,ordered_key
34402,8521273,2021229,2013,34402_2021229_2013
34402,8521273,2021229,2014,34402_2021229_2014
34402,8521273,2021229,2015,34402_2021229_2015
34402,8521273,2021229,2017,34402_2021229_2017
34402,8521275,2021229,2012,34402_2021229_2012
34402,8559208,2021229,2016,34402_2021229_2016
]
;

find_first_value:
load
company & '_'& employeeid as employee_key,
firstsortedvalue(distinct start_item, ordered_key) as first_value
Resident table
group by company & '_' & employeeid;