Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

hencovanee
Contributor II

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

Tags (5)
1 Solution

Accepted Solutions
danielrozental
Honored Contributor II

Re: How to search in range of numbers

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

11 Replies
danielrozental
Honored Contributor II

Re: How to search in range of numbers

Something like this?

hencovanee
Contributor II

Re: How to search in range of numbers

Thanks for your reply Daniel.

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

danielrozental
Honored Contributor II

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

hencovanee
Contributor II

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

Re: How to search in range of numbers

Daniel,

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

Bill

danielrozental
Honored Contributor II

Re: How to search in range of numbers

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

danielrozental
Honored Contributor II

Re: How to search in range of numbers

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

hencovanee
Contributor II

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.

danielrozental
Honored Contributor II

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.

Community Browser