Between

Ahmed

Forums

Hi,
I would like to find an equation that allow me to find a number if it is in a range of numbering 
I attachd a file .xls as un example,
Thank you.

Brian Element Tue, 10/31/2017 - 07:18

Hi Admed,

Thanks for giving an example file.  Try this function with a character field, it will return Yes if the number is between the range and No if it isn't.

@if(@Between(NUMBERS, FROM, TO) = 1, "Yes", "No")

Ahmed Tue, 10/31/2017 - 08:54

Hi Brian,
Thank you very much. 
So this equation work for the same line; but how we can change it to look up for all the line for example if = "NO" go to next value ....
 

Brian Element Tue, 10/31/2017 - 09:39

Hi Ahmed,

You can use the @GetNextValue() function but I am not 100% sure what you are asking.  So what happens if it is NO for the next one, are you just trying to find a match?

Ahmed Tue, 10/31/2017 - 10:04

Yes Brian, i'm just looking to find a match but at one condition is to look up till the final line.
Thank you.

Brian Element Wed, 11/01/2017 - 08:03

Hi Ahmed,

Scott has created a script that should help you out, it is in post #5 from this thread (http://ideascripting.com/forum/joining-tables-when-key-between-two-valu…)

You will have to break out your file into two files, one with the upper and lower limits and another with just the numbers.  For the upper and lower limit file if you don't already have a field that indicates what the range is you can add a recno field to the file so it know how it relates.

Let me know how it goes and if it works for you.

Thanks

Brian

Ahmed Thu, 11/02/2017 - 04:05

Hi Brian,
I followed your recommendations and it worked perfectly 
Thank you very much for your help.

Ahmed Sun, 11/05/2017 - 04:54

Hi Brian
when I run the script; It takes a lot of time, because I tested it on 150 000 lines and it take it 16 hours ! 
How is it possible to make it faster ?
Thank you.

Brian Element Sun, 11/05/2017 - 16:54

There might be a few ways to make it more efficient but I don't think there will be huge savings.  It is the nature of the beast where this is comparing all the records against multiple criteria so it is going to take a long time.  Also this is a script so it is much slower then if this was a built in IDEA function.