Perform @CompIf function

William Yong

Forums

Greetings,
I have a scenario where i need to do some records filtering by fulfiling 3 conditions in a single database file, e.g. Amount < 0, 'Low', Amount > 0 And Amount <=5, 'Moderate' and Amount > 5, 'High'. How am I able to perform those 3 conditions by Low, Moderate & High where in the end, I need to produce a graph based on those key conditions. I try use the @compif function, i.e. @CompIf(Amount < 0, "low", Amount > 0 .AND. Amount <= 5,  "moderate", amount > 5, "high"  ) and it returned a result that I'm not expecting of. How am I suppose to group the 3 conditions? What is the correct @function to use here or any solution/advice for me.
Thanks. William
 

Brian Element Mon, 06/15/2015 - 07:40

Hi William,

I am not sure why your forumla is giving you inconsistent results as it looks correct.  I might write it a bit differently:

@CompIf(AMOUNT < 0, "low", AMOUNT <= 5, "moderate", 1, "high")

So I removed the AMOUNT > 0 as if amount is less than 0 it will be captured by the first part.  The second part would capture anything less than or equal to 5 and the final portion is the default which would be anything that is greater than 5.

Let me know if you are getting inconsistent resutls and what those results might be.

I just looked at your forumula again and it looks like if you have any 0 items they would not be captured, is this what you want?  In my formalu 0 items would be captured as moderate.

Brian

William Yong Thu, 06/18/2015 - 03:51

Thanks Brian.
If I would like to create a new amount virtual field from the same database, group and summarize those results in by 'low', 'moderate' & 'high', what would be my criteria looks like or any other suitable @function that I can use here? I need those summarization info in order to generate a graph.
Cheers, William

Brian Element Thu, 06/18/2015 - 05:53

Hi William,

Did the @compif equation work for you?  If so you could just use this as your virtual field and then do a summarization on the new field.

Brian

William Yong Tue, 06/23/2015 - 00:22

Hi Brian,
When I created the virtual field by inserting the @compif equation, it prompted me 'Error' either in equation or the virtual field. I try to perform the direct extraction with the same @Compif equation, the equation won't work and return the same number of records. I attached my Excel file for your reference. Appreciate to assist with my problem here.
Thanks, William
 

Brian Element Tue, 06/23/2015 - 07:40

Hi William, that error is easy to fix.  What you are returing from your equation is a character, from what I can see of your equation you are returning "red" if the percentage is less than 0 but you have defined you virtual field as numeric, you need to define it as virtual character.  When you get this error it means that the results of your equation are a different type then what your virtual field is expecting, in this instance the virtual field is expecting numeric but you are returning character.

William Yong Fri, 06/26/2015 - 03:31

Hi Brian,
With the working equation, my task now is to create a graph which I had done it in IDEA. However, upon automate it with IDEAscripting, my script "freezes". Not too sure where's the error here. Appreciate to assist to browse through the script and let me know the solution for my script.
Cheers, William

Brian Element Fri, 06/26/2015 - 07:18

Hi William, one problem I see in the GenerateGraph() function is that you have defined field as a string when it should be defined as an object or a field, that might be what is cuasing your problem.

William Yong Sun, 06/28/2015 - 23:42

Hi Brian,
My script still freezes even though I have changed the field to Dim field as field. Could you please assist me to have a look at the script again. 
Thanks.

Brian Element Tue, 06/30/2015 - 12:26

Hi William, well I had a look at it and the problem was that it wasn't freezing but you had the select case statements set-up so that if you clicked on any buttons it wouldn't do anything.  I have changed them so your buttons for your dialogs now work.