Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
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). A simple expression. I want to get the value of col2 for the maximum value of column 1. As such with the data you see this expression will return null. If I remove the last line, it will return 'b1'. I know for a fact, the combination of col1 and col2 will always be unique. Do we have any work around please?

Thanks,

Ram

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Problem with FirstSortedValue when dealing with repeated values

Hi,

please find attached file for solution.  Hope it helps you.

Regards,

Jagan.

View solution in original post

14 Replies
Highlighted
Specialist
Specialist

Re: Problem with FirstSortedValue when dealing with repeated values

Hi Ram,

In expression make Col3 as Decreasing i.e do shortening the values.

rgds

Ashwani

Highlighted
Not applicable

Re: Problem with FirstSortedValue when dealing with repeated values

Sorry I don't understand.

Highlighted
Master II
Master II

Re: Problem with FirstSortedValue when dealing with repeated values

Hi.

There two ways: datamodel changing or complex expression.

If you're sure that these line are always the same - store it as one line in a separate table. So that you can use standart FirstSortedValue.

Or you can write a complex expression with aggr, Max and Only functions, using the same logic as FirstSortedValue. But this approach is not flexible when you need to add different dimensions in your charts. 

Highlighted
Not applicable

Re: Problem with FirstSortedValue when dealing with repeated values

If I go down the separate table route, how do I avoid a link to that table. e.g. in my case if i create a table with just distinct values for col1 and col2 how do I avoid the table from getting linked to the existing tables?

Highlighted
Not applicable

Re: Problem with FirstSortedValue when dealing with repeated values

Hi,

firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]])

By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL.

Here  Inline Table Contain

temp:load * inline[
col1, col2, col3
1, a1, 1000
2,c1, 2000

3, b1, 3000

3,b1,4000
]
;

If  I  use Firstsortvalue(col1,-col2) then this will gives blank value. As per function functionality.

If I remove one row. Then my inline function will be

temp:load * inline[
col1, col2, col3
1, a1, 1000
2,c1, 2000
3,b1,4000
]
;

Firstsortvalue(col1,-col2)

The out put will be b1 that is desired out put.

Regards,

Starwberry

Highlighted
Not applicable

Re: Problem with FirstSortedValue when dealing with repeated values

Yes... i believe this function is working like the same.

The answer is b1

Highlighted
Not applicable

Re: Problem with FirstSortedValue when dealing with repeated values

Thanks Mohit. Yes, I understand the problem absolutely. Looking for workarounds.

Highlighted
Not applicable

Re: Problem with FirstSortedValue when dealing with repeated values

If there is no workaround, I'll have to get the value of col2 of the latest record in my load script in a variable and use it. But I have kept it as the last resort.

Highlighted
Specialist III
Specialist III

Re: Problem with FirstSortedValue when dealing with repeated values

Hi,

If there are duplicates then the firstsortedvalue will return a Null.

thanks,

Rajesh Vaswani