FirstSortedValue does not deliver what I expect

[draghici1109]

Dear all,

I face the following challenge, where I do not know how to come to an end / success. I want to use FirstSortedValue to get only one record per Personalnummer.

Here is the code:

FirstSortedValue:
NoConcatenate
LOAD
Personalnummer,
[ID Stelle],
AB,
FirstSortedValue(distinct BIS,-AB) AS BIS,
//A minus sign precedes the sort_weight argument, so the function sorts the largest first.
[Anteil (in %)]
FROM [..\DATA\ImportStellenbesetzungen1.qvd](qvd)
Group by Personalnummer,[ID Stelle],AB, [Anteil (in %)]
;
Store [FirstSortedValue] into [..\DATA\FirstSortedValue.qvd](qvd);

However, the Result in FirstSortedValue.qvd does not deliver one record per Personalnummer... it seems like it would not work properly. I cannot see why FirstSortedValue does not work in this case as I expect.

I attached the input file and and the output file.

Thank you in advance.

[Or]

Perhaps I followed along incorrectly, but I would expect one record per Personalnummer,[ID Stelle],AB, [Anteil (in %)] since those are the Group By fields. FirstSortedValue() does not impact the number of records, it just pulls a specific result based on the grouping, field, and sort.

[draghici1109]

Thank you for your quick reaction1

I am not able to follow you, because from the help for this function, I should expect that FirstSortedValue deliever only one value, see screenshot:

[Or]

In that example, the data is grouped by customer, so one row returns per customer. In your example, you grouped by three fields, so you would get one row per combination of those three fields. FirstSortedValue() never returns more than value because it can't - it'll either return a single value, or it'll return null.

[draghici1109]

ok, now we are on the same page regarding how FirstSortedValue should work.

Regarding the group by: i tried only group by Personalnummer, yet I got following error:

[Or]

Your fields need to either be in the Group By, or they need to be in an aggregation function of some sort. If you leave a field out of the Group By but don't give it an aggregation function, Qlik has no way of knowing what to do when that field has multiple values per group. This is the same as with SQL, if you've worked with that.

[marcus_sommer · Accepted solution]

I assume firstsortedvalue() is rather not the right function else a max() might be more suitable - and maybe even within a second load, like:

t: load * from Source;
inner join(t)
load Personalnummer, [ID Stelle], max(AB) as AB
resident t group by Personalnummer, [ID Stelle];

Beside of this the 100 as value for all % looked not right ... and the max. of BIS is a bit high - probably from the year 9999 ...

If I look again it seems that you want to generate (it's already in the qvd ...) respectively to correct BIS ... In this case you should use interrekord-functions like previous() and peek() within an appropriate sorted resident-load. 

[draghici1109]

I solved the problem as you suggested as an algorhythm. However I still used FirstSortedValue two times, concatenated the results and is the same result.

interrecord functions like previous and peek should work too.

Thanks Markus and Thanks OR! Great to be part of this Qlik-Community!