Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
draghici1109
Creator
Creator

FirstSortedValue does not deliver what I expect

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.

Alexandru Draghici
BICC at Komm.ONE
Labels (2)
1 Solution

Accepted Solutions
marcus_sommer

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. 

View solution in original post

7 Replies
Or
MVP
MVP

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
Creator
Creator
Author

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:

draghici1109_0-1679911871623.png

 

Alexandru Draghici
BICC at Komm.ONE
Or
MVP
MVP

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
Creator
Creator
Author

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:

draghici1109_0-1679912293570.png

 

Alexandru Draghici
BICC at Komm.ONE
Or
MVP
MVP

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

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
Creator
Creator
Author

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!

Alexandru Draghici
BICC at Komm.ONE