How to search in range of numbers

[Anonymous]

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.

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

Could one you explain to me how to do this?

Cheers,

Henco

[danielrozental]

Something like this?

[Anonymous]

Thanks for your reply Daniel.

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

[danielrozental]

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))

[Anonymous]

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_number	Data
2.050	2.075	F
2.800	3.000	G
1.875	1.900	D

[Bill_Britt]

Daniel,

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

Bill

[danielrozental]

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

[danielrozental · Accepted solution]

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

[Anonymous]

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.

[danielrozental]

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.