Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Oh, ok, thanks Bill, here's the document again.
Something like this?
Thanks for your reply Daniel.
Unfortunately I'm getting an error while opening your QV document.
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))
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 |
Daniel,
I tried to open the document and I also recieved an error.
Bill
Are the tables associated at all? If believe it should work anyway
Oh, ok, thanks Bill, here's the document again.
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.
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.