Lookup With Value Between Two Lookup Fields

[dcsteve24]

I have a lookup table which contains a varying low value and a high value for many rows, along with the desired value I wish to grab. For example (these are just dummy values as the actual CSV is much larger):

Low	High	Desired Value
1000	1499	550
1500	1599	220
1600	1999	700

I work through my search input and come to a number value I want to test in the range of the low and high. (low < myNumber AND high > myNumber), to then pull the desired value.

The typical input for "myNumber" is a multivalue, containing typically three or more. I've found I have more success breaking this up with mvexpand and rejoining it again later with stats. Im just noting this in case it provides any value for the answer. So my input before I need the value may look like this:

Field1	Field2	Field3	Field4	myNumber	Field5
Blah	Bleh	Meh	Whatever	1200 1400 1520	Stuff
Just	Dummy	Data	Here	1510 1625 1780	Still

or if i break it apart with mvexpand, like this:

Field1	Field2	Field3	Field4	myNumber	Field5
Blah	Bleh	Meh	Whatever	1200	Stuff
Blah	Bleh	Meh	Whatever	1400	Stuff
Blah	Bleh	Meh	Whatever	1520	Stuff
Just	Dummy	Data	Here	1510	Still
Just	Dummy	Data	Here	1625	Still
Just	Dummy	Data	Here	1780	Still

I want to be able to add the Desired Value to my table based on if myNumber falls between the Low and High. The number does not match the Low or High, but will always fall between one of the ranges.

So my final output should look like this when im done:

Field1	Field2	Field3	Field4	myNumber	Field5	DesiredValue
Blah	Bleh	Meh	Whatever	1200 1400 1520	Stuff	550 550 220
Just	Dummy	Data	Here	1510 1625 1780	Still	220 700 700

I can't seem to figure out how to go about this. I have no problem breaking apart the multivalue and rejoining it, I just can't figure out how to do a lookup that falls within a two fields...

Routes Ive tried:

- If I use inputlookup I can use the where command to filter out values just fine. However, if I try mapping the same search to map search="| inputlookup search here | where low < $myNumber$ AND high > $myNumber$" it doesn't work, meaning I cant seem to find a way to link the two together.

- Join doesn't allow passing of vars and there isn't a shared field to join on, so no love there

- the lookup command but it doesn't have any evaluations built in that I can find; seems to only work for exact matches.

I appreciate any assistance I can get.

[rnowitzki · Accepted solution]

Hi @dcsteve24 ,

Thanks for the clarification.

I found a solution for this, using map command.

| makeresults 
| eval myNumber="1200 1400 1520 1520 1625 1780"
| makemv myNumber
| mvexpand myNumber
|  map search="|inputlookup low_high.csv |  where high > $myNumber$ AND low < $myNumber$ | eval myNumber=$myNumber$ | fields - high low"
| stats values(myNumber) by desired

You only need the lines starting with the map command, the ones above are just to make a table with the "myNumber" field.

My low_high.csv looks that I used looks like:

low     high  desired
1000 1499 550
1500 1599 220
1600 1999 700

BR
Ralph

[dcsteve24]

Hi Ralph,
Thanks! I was really close with the map command but failed to think of simply adding a few evals with the properties I need to keep. That's brilliant!

Thank you so much.

[rnowitzki]

Hi @dcsteve24,

Sorry, maybe it's me, but I don't understand your requirement. What is the relation between myNumber, low, high and desired value?

In the table you show as final output, the desired values are not within the range of the values in "myNumber".

But in the command that you provided as one of your attempts, it looks like you want to check if "myNumber" is between low and high  (where low < $myNumber$ AND high > $myNumber$")

Could you please explain in other words, how you would like to come from the initial csv lookup to your final output?  

Thanks 
Ralph

[dcsteve24]

Hi Ralph,
myNumber isn't the range, its a number that will fall between the ranges; basically:
- 1200 (myNumber) is between 1000 (low) and 1499 (High) so it should return the desired value (500).
- 1400 (myNumber) is between 1000 (low) and 1499 (High) so it should return the desired value (500). 
- 1520 (myNumber) is between 1500 (low) and 1599 (high) so it should return the desired value (220)
- so on so forth

To sum it up in a algebra fashion:

- I have a lookup table with a high value (Y) and low value (Z) and my desired value (A). The high and low together make a range Y-Z. In the example, one would be 1500-1599 for a range.

- I have a value that falls in one of the Y-Z ranges of the lookup table. I identified this as MyNumber in the post but we'll say X here.

- So I need a way to take X, evaluates that it falls between Y and Z (Y < X AND Z > X) from the lookup table, and returns my desired field (A) from the lookup table. 

Hope that helps clear it up?

Unfortunately I am using dummy data because of protections on the information. So I cant give you the exact data and scenario I need this for, but if we can solve the above I can work from there. I simply cant figure out how to evaluate between a range in a lookup table to get my desired value.