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: 
Not applicable

firstSortedValue NULL

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.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

15 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Use MinString or MaxString in that case

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

Celambarasan

Not applicable
Author

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
Partner - Champion
Partner - Champion

Hi,

     Can you Post your FirstSortedValue expression?

Celambarasan

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Try with this

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

Celambarasan

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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.

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you post some sample data and your desired result?

-Rob

Not applicable
Author

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.