Problem with FirstSortedValue when dealing with repeated values

[#1]

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

[ashwanin]

Hi Ram,

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

rgds

Ashwani

[#3]

Sorry I don't understand.

[whiteline]

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. 

[#5]

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?

[#6]

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

[#7]

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

The answer is b1

[#8]

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

[#9]

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]

Hi,

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

thanks,

Rajesh Vaswani