n-ésim greater value with repeated values on the dataset

[GLicks_DG]

Hey guys, I'm having some troubles with MAX and MIN function.

For example, if i have the folowing table:

Dim1
5
4
3
3
3
2
1

Max(Dim1, 1) returns 5 - ok;

Max(Dim1, 2) returns 4 - ok;

Max(Dim1, 3) returns 3 - ok;

Max(Dim1, 4) returns 2 ->>>> I would like it to return 3 again.

Another solution that could work for me is a way to get the value from a specific row. So if I need the 4th greater value in Dim1 I could order it and then load the valeu from line 4. But I need to do it not by a where clause, since in the same load i am loading some more info regarding the whole column Dim1.

Sorry if Im not being too clear, I open to give any extra info. Thanks in advance!

[olivetwist]

EDIT: Never mind, i read more thoroughly. I'll be back in a few with my answer.

Are you trying to do this in the script or within an object in design?

[olivetwist]

I was able to make it work by using:

temp2:
load
if(RowNo()=4,Dim1) as test
Resident TEMP;

TEMP = name of my inline using your data

[GLicks_DG]

I keep getting an 'Invalid Expression' error if try to use your solution. The only way I managed to actually get the expected outcome was loading with the clause "Where RowNo() = ..." (where the 3 dots are the n in n-ésim). Unfortunatelly that requires me to make a lot of adjustments in the script..

Isn't there an easier way to get a specific valeu from a given line in a given column? I mean like a Vector[Position] or something like it? I tryied  'FieldValue()' and even 'Peek()' functions but couldn't manage to get it..

[olivetwist]

are you loading other fields as well? doing any aggregate functions that require a group by? 

Peek is used to assign a variable to a specific value in a field. It is used outside of a load statement against an existing table.

[GLicks_DG]

I am Loading a couple more fields, but I managed to store what i need from them in variables. The solution to get the n-ésim greatest value though was first ordering my data and then loading with a where clause on the row number.

@olivetwist Thank you for all the help!

[olivetwist · Accepted solution]

Well, it works for me as a field in load. i suppose your data may not like that method and need the where. I didn't mention using where since your original post said you didn't want to.