topic Re: FirstSortedValue Returns Null in QlikView

FirstSortedValue Returns Null

Wed, 13 Jan 2016 16:35:03 GMT

I have looked at several threads about FirstSortedValue function and have followed the steps suggested in various scenarios, but for some reason I'm not always getting a value returned in my FirstSortedValue expression. I have tried this both in the script and in the chart expression but nothing is resolving the issue.

The scenario is this: For each policy I have a field called StatusLabel that describes the status of the policy, and a SortID field that identifies how i want to sort the statuses. For each customer, I want to return the FirstSortedValue of the StatusLabel sorted by SortID in reverse order (the highest numbered SortID). My expression is:

FirstSortedValue(StatusLabel, -SortID)

Sometimes I get a value and sometimes I don't, and I can't figure out what the difference is. I have attached a sample document that is filtered to a customer that clearly has several policies of varying statuses associated, but the FirstSortedValue function is not returning a value either in the script by returning a value in the PersonStatus field, or in the chart expression where I tested it.

Can anyone see what I'm missing?

Thanks

Re: FirstSortedValue Returns Null

swuehl — Wed, 13 Jan 2016 16:38:27 GMT

Try

=FirstSortedValue(DISTINCT StatusLabel, -SortID)

Re: FirstSortedValue Returns Null

sunny_talwar — Wed, 13 Jan 2016 16:39:40 GMT

You are using a field which isn't even available in your database -> Rank. What is your expected Status from these and what is the logic?

Re: FirstSortedValue Returns Null

Wed, 13 Jan 2016 16:42:55 GMT

Sorry, i changed the name for the field Rank to 'SortID' in the script thinking maybe the issue was that I used a QV keyword as a field name. I forgot to update that expression to use the revised field name before i saved and uploaded the sample doc.

Re: FirstSortedValue Returns Null

Wed, 13 Jan 2016 16:44:10 GMT

Interesting... that works like a charm, although QV doesn't seem to want to recognize that DISTINCT is valid for use in that expression. Thanks, I knew it was probably something that simple!

Re: FirstSortedValue Returns Null

Gabriel — Wed, 13 Jan 2016 16:48:36 GMT

Hi,

First, the Rank field is been renamed in the script to SortID. So in object CH03 you have to change the -Rank to -SortID. Now it should read FirstSortedValue(StatusLabel, -SortID)

Secondly, some customer ID is frequency count is more than 1. So whenever Customer ID frequency count is more than 1 FirstSortedValue will return Null value '(-)'

May be use this

FirstSortedValue(DISTINCT StatusLabel, -SortID)