topic Re: Problem with FirstSortedValue when dealing with repeated values in QlikView

Problem with FirstSortedValue when dealing with repeated values

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Problem with FirstSortedValue when dealing with repeated values

arulsettu — Wed, 02 Aug 2017 07:43:39 GMT

try this

fist text box

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

second text box

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

Re: Problem with FirstSortedValue when dealing with repeated values

tresB — Wed, 02 Aug 2017 07:51:34 GMT

Exp1: =FirstSortedValue(DISTINCT col2, -col1)

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

Re: Problem with FirstSortedValue when dealing with repeated values

effinty2112 — Wed, 02 Aug 2017 08:30:19 GMT

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

Kushal_Chawda — Wed, 02 Aug 2017 12:31:52 GMT

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,',')

Re: Problem with FirstSortedValue when dealing with repeated values

sasiparupudi1 — Thu, 03 Aug 2017 11:37:12 GMT

use distinct qualifier in your firstsortedvalue

FirstSortedValue(distinct col2,-col1)

FirstSortedValue(distinct col3, -col1)

hth

Sasi