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

[whiteline]

To separate table just give differ field names.

Why do you want to make it separate ? I thiught it should be connected with the other data through col2 as a key.

So in one table you have col1 and col2. In the other col2 and col3.

[jagan · Accepted solution]

Hi,

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

Regards,

Jagan.

[#4]

Thanks Jagan. That is perfect.

[#5]

However one question. Any means by which we can do this without the Unique ID surrogate key?

[whiteline]

Easily:

=Only({<col1={$(=Max(total col1))}>} col2)