Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Problem with FirstSortedValue when dealing with repeated values

Hi All,

I have a problem with FirstSortedValue. Please help.

temp:

load * inline

[

col1, col2, col3

1, a1, 1000

2,c1, 2000

3, b1, 3000

3,b1,4000

];

I have data as above. And in a text box I have =FirstSortedValue( col2, -col1), The result i want is b1 but it does not work.

In another box I have =FirstSortedValue( col3, -col1), The result I want is 3000, 4000 but it does not work either.

Can anybody help me? Thank you in advance


Best,

Junfei

5 Replies
arulsettu
Honored Contributor III

Re: Problem with FirstSortedValue when dealing with repeated values

HI

try this

fist text box

FirstSortedValue(col1,aggr(sum(col3),col2,col1))

second text box

FirstSortedValue(col3,-aggr(sum(col3),col2,col1))

MVP
MVP

Re: Problem with FirstSortedValue when dealing with repeated values

Exp1: =FirstSortedValue(DISTINCT col2, -col1)

Exp2: =FirstSortedValue(Aggr(concat(DISTINCT col3, ','), col1), -Aggr(col1,col1))

Capture.PNG

effinty2112
Honored Contributor

Re: Problem with FirstSortedValue when dealing with repeated values

Hi Junfei,

Here's an alternative for your second expression. I dislike FirstSortedValue because of the way it treats ties for the place in the ranking we're looking for

concat(Aggr(if(Rank(sum(col1)) =1, col3),col3),',') = 3000,4000

Cheers

Andrew

Re: Problem with FirstSortedValue when dealing with repeated values

Your expression is correct, but you just need to add Distinct keyword as you have multiple value for max of col1

FirstSortedValue(DISTINCT col2, -col1)


for second expression you can use the below instead of complex firstsortedvalue function


=Concat({<col1={"$(=max(col1))"}>}col3,',')

sasiparupudi1
Honored Contributor III

Re: Problem with FirstSortedValue when dealing with repeated values

use distinct qualifier in your firstsortedvalue

FirstSortedValue(distinct col2,-col1)

FirstSortedValue(distinct col3, -col1)

hth

Sasi