Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

View solution in original post

14 Replies
ashwanin
Specialist
Specialist

Hi Ram,

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

rgds

Ashwani

Not applicable
Author

Sorry I don't understand.

whiteline
Master II
Master II

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. 

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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

The answer is b1

Not applicable
Author

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

Not applicable
Author

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.

rajeshvaswani77
Specialist III
Specialist III

Hi,

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

thanks,

Rajesh Vaswani