Burst range

10 posts / 0 new
Last post
Ahmed
Offline
Joined: 09/20/2017 - 05:45
Burst range

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
Offline
Joined: 07/31/2012 - 13:20

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
Offline
Joined: 07/31/2012 - 13:20

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
Offline
Joined: 09/20/2017 - 05:45

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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

Ahmed
Offline
Joined: 09/20/2017 - 05:45

Thank you Brian.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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
Offline
Joined: 09/20/2017 - 05:45

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

Images: 
Ahmed
Offline
Joined: 09/20/2017 - 05:45

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

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Thanks for letting me know.  I will add some additional error checks in the script to test for that.

Brian