find firstsortedvalue (or minstring) from table per customerid

[curiousfellow]

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

[brunobertels]

Hi 

In script you nedd a group by clause when using firstsortedvalue 

So : 

load customerid,

firstsortedvalue(valuefield,sortfield)

resident table 2 group by customerid ;

[curiousfellow]

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

[Antoine04]

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

[BrunPierre · Accepted solution]

Hi @curiousfellow how about as below?

FirstValue(start_item) as first_value

or

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

[curiousfellow]

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

[curiousfellow]

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;