11 Replies Latest reply: Mar 1, 2013 10:05 AM by Henco van Ee

# How to search in range of numbers

Hi all,

Question about search in a range of numbers.

Let's say I have the number 2.050. I would like to find the nearest number and then select the data in the second column.

NumberData
1.000A
1.550B
1.800C
1.900D
2.000E
2.075F
3.000G

Could one you explain to me how to do this?

Cheers,

Henco

• ###### Re: How to search in range of numbers

Something like this?

• ###### Re: How to search in range of numbers

Unfortunately I'm getting an error while opening your QV document.

• ###### Re: How to search in range of numbers

Are you working with Personal Edition?

Here are my expressions, vValue holds the value you're looking for the nearest one

Nearest Value

firstsortedvalue(Number, fabs(Number-vValue))

Data

firstsortedvalue(Data, fabs(Number-vValue))

• ###### Re: How to search in range of numbers

Thanks Daniel.

I'm using QV11.1 with Named User cal, so I'm not sure what the problem is.

I used your expressions, they are perfect.

Since I do have a list of numbers I would like to find, is it possible to do this without a variable?

The output I would like to have is:

Number_input
Nearest_numberData
2.0502.075F
2.8003.000G
1.8751.900D
• ###### Re: How to search in range of numbers

Are the tables associated at all? If believe it should work anyway

• ###### Re: How to search in range of numbers

Daniel,

I tried to open the document and I also recieved an error.

Bill

• ###### Re: How to search in range of numbers

Oh, ok, thanks Bill, here's the document again.

• ###### Re: How to search in range of numbers

Thanks, that works for me.

In the help I read this:

If more than one value of expression share the same lowest sort-order, the function will return NULL.

By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

Could you please tell me how to add this to the expression?

Thanks a lot.

• ###### Re: How to search in range of numbers

You can see that in the help samples.

firstsortedvalue ( distinct PurchasedArticle, OrderDate )

If you have problems with ties just add a rand() to the input values or something like that.

• ###### Re: How to search in range of numbers

Ah of course, thanks a lot for helping me out.

Cheers,

Henco

• ###### Re: How to search in range of numbers

Still one question: the input number is a sum in my live app.

It is not possible to change the expression like this:

firstsortedvalue(Number, fabs(Number-sum(Number_input)))

Is there another way to do that?