firstSortedValue NULL

[#1]

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.

[CELAMBARASAN]

Hi,

     Use MinString or MaxString in that case

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

Celambarasan

[#3]

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".

[CELAMBARASAN]

Hi,

     Can you Post your FirstSortedValue expression?

Celambarasan

[CELAMBARASAN]

Hi,

     Try with this

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

Celambarasan

[rwunderlich · Accepted solution]

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

[#7]

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.

[#8]

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

[rwunderlich]

Can you post some sample data and your desired result?

-Rob

[#10]

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.