Burst range

Ahmed

Forums

Hi,
I need to find how automatically with idea (using formula or script ) that help me to burst a list of number that are between (upper and lower )
I have attached a file .xls as an example
Thank you

Steven Luciani Tue, 07/31/2018 - 07:45

Hello,

To do this with an equation you would combine the @compif and @between functions in IDEA per the example below.

@compif(@between(NUMBER,11250000,11250009)=1,"ALPHA",@between(NUMBER,15000000,15000010)=1,"BETA",1,"")

 where: Number is the name of the field that contains your value.

This equation can be expanded for many more "bursts" by simply copy and pasting the @between string and adjusting the number range and desired character result. The ,1, "" at the end of the compif tells the function that all testing is complete and if it comes across a number that doesen't meet all the burst criteria simply leave that record blank. You could also you some other character designation like "OUTLIER" instead of a blank.

If you had hundreds of range combinations this equation will beome very cumbersome. You would want to conduct a ranged join that would require an IDEAScript. I will leave that answer to Brian or one of the better scripters in the forum.

Steve

Steven Luciani Tue, 07/31/2018 - 07:54

After I posted the equation I recalled that I had seen a script like this before created by a work colleague.

I attached it here. It is a zip archive that contains the script and two sample IDEA files to demonstrate how it works.

Good luck with your task.

Steve

Ahmed Tue, 07/31/2018 - 09:04

Hello Steve, 
First of all, thank you very much for your answer, however; actually I have a large volume of numbers to treat and actually the formula will not help me too much, because I need a script .; and as for the script you sent me, I already use it in other need.
Thank you.
Ahmed.

Brian Element Tue, 07/31/2018 - 10:45

You will definitely need a script for this, should be too hard, I will just have to find a bit of time to build it.  I will get back to you when I have something.

Brian

Brian Element Tue, 07/31/2018 - 14:27

Hi Ahmed, can you try this script out.

The client field can be either a character or numeric field.  The upper and lower limits must be an integer, not sure what it would do with decimals, probably ignore them.

The script worked with your example.

Let me know how it work and if you have any suggestions and I will post it in the main library.

Thanks

Brian

Ahmed Wed, 08/01/2018 - 04:48

Hi Brian,
Here's the error message that I get when I run the script 
Thank you.

Ahmed Wed, 08/01/2018 - 06:16

Hi Brian,
After doing some check on my database, now; the script work perfectly. 
The reason of my presvious error was in the lower and upper number that started with zero; and this is why it didn't work but now it's ok
Thank you very much Brian :)
Regards.
Ahmed