topic firstSortedValue NULL in QlikView

firstSortedValue NULL

Thu, 05 Apr 2012 11:30:05 GMT

Hi there,

I'm using firstSortedValue but it doesn't work because some of my data has the same "order" and so it returns me null.

Is there a way to "emulate" firstSortedValue with data havaing the same order ? So instead of returning null it'll return the first result of the set.

Thx.

firstSortedValue NULL

CELAMBARASAN — Thu, 05 Apr 2012 12:04:55 GMT

Hi,

Use MinString or MaxString in that case

MaxString({<Date={"$(Max(Date))"}>} Field)

Celambarasan

firstSortedValue NULL

Thu, 05 Apr 2012 13:45:18 GMT

Thanks, tried that but it doesn't seem to work, the results are empty.

Here is what I used :

MinString({<productDate={"$(Max(productDate))"}>} totalProductPrice)

productDate and totalProductPrice come from the same table and are filtred by a "productID".

firstSortedValue NULL

CELAMBARASAN — Thu, 05 Apr 2012 13:47:27 GMT

Hi,

Can you Post your FirstSortedValue expression?

Celambarasan

firstSortedValue NULL

CELAMBARASAN — Thu, 05 Apr 2012 13:48:31 GMT

Hi,

Try with this

MinString({<productDate={'$(=Date(Max(productDate)))'}>} totalProductPrice)

Celambarasan

Re: firstSortedValue NULL

rwunderlich — Thu, 05 Apr 2012 17:50:38 GMT

It depends on your rule for selecting from the duplicate sort values. You can add the DISTINCT keyword and you'll get the first value by load order. For example:

Date, Value

1/1, 100

1/1, 150

=firstsortedvalue(DISTINCT Value, Date)

will return 100.

If you have another rule, such as highest/lowest value, don't use DISTINCT but modify sort value with a bias of the Value itself.

=firstsortedvalue(Value, Date+(Value/1000000))

will return 150.

Note tha the bias does not have to come from the Value -- it could be calculated on any other connected field.

-Rob

http://robwunderlich.com

Re: firstSortedValue NULL

Fri, 06 Apr 2012 07:21:24 GMT

Thanks, that's what I was looking for.

I tried it before but it seems that QlikView doesn't recognize the "distinct" keyword and think it's a syntax error but it works.

Re: firstSortedValue NULL

Fri, 11 Jan 2013 15:07:49 GMT

Hello everyone.

Another question about FirstSortedValue:

What if the first parameter in FirstSortedValue function is string? so we can't use (Value / 1000000) as types are not compatible?

Globaly the problem is that we (my project) will definitely have duplicate records which we need to sort. And everytime FirstSortedValue sees duplicate it returns NULL. If we put DISTINCT keyword in this function we'll get a reduced number of records what is not acceptable. Is there a way to implement sorting in expressions without this function? or in a completely different way?

Thanks

Re: firstSortedValue NULL

rwunderlich — Tue, 22 Jan 2013 07:38:42 GMT

Can you post some sample data and your desired result?

-Rob

Re: firstSortedValue NULL

Tue, 22 Jan 2013 09:44:16 GMT

For instance we have 3 students Aaa, Bbb, Ccc. Aaa has 5 Scores, Bbb and Ccc have 4. In table view:

Student Score

Aaa 5

Bbb 4

Ccc 4

If we don't use DISTINCT we get empt.

If we use FirstSortedValue with DISTINCT we get the following results:

Student Score

Bbb 4

Aaa 5

So a student Ccc is gone, as he has the same Score as Bbb and we only take distinct here.

And we want to have all the records sorted as usual and also have all the original records in place.

So like this:

Student Score

Ccc 4

Bbb 4

Aaa 5

Alphabet sorting is irrelevant now, we just need to keep the identical records in place.

Re: firstSortedValue NULL

rwunderlich — Tue, 22 Jan 2013 16:34:58 GMT

Do you want to display just the max score for each student? In that case, use

=max(Score)

Am I understanding your requirement?

-Rob

Re: firstSortedValue NULL

Tue, 22 Jan 2013 16:49:10 GMT

No.

For my example: there are 3 students. I want those 3 students sorted by thier Score value.

If you implement that with FirstSortedValue without DISTINCT you won't get anything as a result.

If you implement that with DISTINCT you get only two different records.

So I need to avoid described situations, becuase we definitely will have identical scores for students and I don't to loose any of them and more over I want to work at all.

Remember how sorting works in MySQL for instance. You have a table with 3 records. You sort this table and you get all 3 records even if there identical values in those records.

Does it make any sense?:)

Re: firstSortedValue NULL

prieper — Tue, 22 Jan 2013 17:00:18 GMT

Did you check on the ORDER BY-statement?

Peter

Re: firstSortedValue NULL

Tue, 22 Jan 2013 17:06:08 GMT

AFAIK Order By statement is suitable for a script, not for expression.

And I need to provide sorting functionality at runtime, when script has already been run, that means in expression.

Re: firstSortedValue NULL

rwunderlich — Tue, 22 Jan 2013 17:32:57 GMT

How about sorting the column by expression?

Re: firstSortedValue NULL

Tue, 22 Jan 2013 17:40:09 GMT

The thing is I need to sort the collection of data, not the table object by it column.

Basically what I want: There is a collection with 3 items, I need to sort it with qlikview and return the result one record at a time, just like a FirstSortedValue has a third parameter, which indicates the index inside the sorted collection.