Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Get field value corresponding to the minimum value of another field

Hi Experts,

I have a simple table like so:

RecordIDData1Data2
10013
1001520
10011115
1001175

I have a variable, vData1Range=15. I want to get the single value from Data2 where Data1<=vData1Range and where Data1 is the minimum and where Data2 is not null. The result using above example should be 20 (because minimum Data1 that is <=15 and where Data2 is not null is 5, and we can see that when Data1=5, Data2=20.

Thanks in advance!

6 Replies
maxgro
MVP
MVP

in a textbox

=SubField(Concat({$ <Data2={">0"},Data1={"<=$(vData1Range)"}>} Data2, ',', Data1), ',', 1)

swuehl
MVP
MVP

Or maybe

=FirstSortedValue({<Data2 = {"*"}, Data1 = {"<=$(vData1Range)"} >}Data2, Data1)

(

I think the Data1 filter is maybe not even necessary.

Using FirstSortedValue() may show issues when there are duplicate values of Data1 in your table

)

MarcoWedel

another one:

=Only({$<Data2={"=Data1=Min({$<Data2={""=Len(Data2)""}>} Total Data1)"}>} Data2)

hope this helps

regards

Marco

Anonymous
Not applicable
Author

Hi Marco, how do I incorporate the value of variable vData1Range in that formula? Value for Data2 should be that of minimum Data1 that is less than or equal to vData1Range.

swuehl
MVP
MVP

Value for Data2 should be that of minimum Data1 that is less than or equal to vData1Range.

Isn't this just the minimum value of Data1? Or could your variable threshold be lower than the minimum value of the set of Data1 values?

Anonymous
Not applicable
Author

The relevance of vData1Range is so that it does not look beyond that range. So for example, the minimum Data1 that has a corresponding Data2 value happens to be 20, but vData1Range=15 then the code should not return a value.

So far maxgro's approach above is actually looking good, but testing it further.