Technical Support Forum Index
Technical Support Forum
Access ChemAxon scientists and developers here. For registration and login issues contact website support.

Support Ticket System is replacing forum

This forum was converted into a searchable archive. You cannot add posts here any more. For support please use our new Ticket System.

Create your first ticket
Calculated Field helper functions
To watch this topic for replies  Register (enables digests) or give email address:
This topic is locked: you cannot edit posts or make replies.
Display posts from previous:   
    View previous topic :: View next topic    
Author Message
Evert

Joined: 15 Feb 2008
Posts: 174

View user's profile

Back to top
Link to postPosted: Sun Oct 16, 2011 7:35 pmPost subject: Calculated Field helper functions Reply with quote

Hi,

I'm beginning to find the Calculated Fields quite useful, but find there are some limitations. In the Help documentation it says:

Helper functions

There is a hidden "gotcha" in the "A + B" example. Whilst it works fine when values are defined for A and B, the script will cause an error when either A or B are null (missing values). Although these errors will be handled and you will end up with empty values, this is not an ideal solution. So to handle this we provide some "helper" functions in the script that make things easier. These are functions like sum(), minus(), multiply(), divide(), avg(), concat() that can be used to simplify writing functions that are more error tolerant.

However, when I add a subtraction using the minus() function, I still get an empty value if one of the parameters to subtract is missing. Do I need to more Groovy code to prevent this?

Also, I wondered if it would be possible to add 'min' and 'max' to the variable types for Calculated fields (now there is the choice of single, sum, avg, list, and str_cat). Having 'min' and 'max' would allow one for example to check a date field for when it last has been changed. Maybe having 'standard deviation' would be useful as well?

Cheers,

Evert

Tim
ChemAxon personnel
Joined: 05 Oct 2004
Posts: 1703

View user's profile

Back to top
Link to postPosted: Sun Oct 16, 2011 8:16 pmPost subject: Reply with quote

 

However, when I add a subtraction using the 
minus() function, I still get an empty value if one of the parameters to
 subtract is missing. Do I need to more Groovy code to prevent this?

Do you mean a calculation like
A - B - C - D
and (let's say) three of the values are defined and one isn't?
You are wanting the missing values to be substituted with zero rather than the answer being null?

If so then this can definitely be done with Groovy code. Please clarify, and we can assist with generating such a function.

Also, I wondered if it would be possible to add 
'min' and 'max' to the variable types for Calculated fields (now there 
is the choice of single, sum, avg, list, and str_cat). Having 'min' and 
'max' would allow one for example to check a date field for when it last
 has been changed. Maybe having 'standard deviation' would be useful as 
well?

Yes, we can definitely add extra items. We don't want the list to get ridiculously long as all can be done in Groovy quite easily. But simple extras like those would make sense.

Tim

 

 

Evert

Joined: 15 Feb 2008
Posts: 174

View user's profile

Back to top
Link to postPosted: Mon Oct 17, 2011 9:08 amPost subject: Reply with quote

Hi Tim,

Yes, that is what I meant. I used the function minus(A, B), and in some cases there is no value defined for B, leading to an empty solution. In those cases I would like B to be treated as if it were zero, so that the result would be equal to A. The Help text suggests that using these helper functions should prevent this empty solution behaviour from happening but it apparently doesn't (always) work?

So if you could provide me with Groovy code to do this, that would be great. Beware though that I've never used Groovy and in general am not much of a programmer...so I'm curious what such code would look like and how complicated or simple it is!

Thanks,

Evert

Tim
ChemAxon personnel
Joined: 05 Oct 2004
Posts: 1703

View user's profile

Back to top
Link to postPosted: Tue Oct 18, 2011 6:02 pmPost subject: Reply with quote

Hi, in the simple case let's assume that you had 2 variables, a and b, and you wanted to calculate a - b, but where values of b were missing you wnated to assume a value of zero for b.

You can do this with a calculation script like this:

a - (b ? b : 0)

In general, wherever you need to reference a variable x you could replace it with (x ? x : 0).

This means if x has a value then use x, otherwise use zero.
To be more precise, if x is true then x, otherwise zero.
Groovy has a quite flexible interpratation of "true" (known as Groovy truth) and in this case having no value (being null) equates to false, and having a value equates to true. As a gotcha, a value of zero is also false, but in this case its OK as you use the value of zero instead!) But in other cases you migth need a more verbose syntax like

(x != null ? x : 0)

So wherever you have a varaible that you wat to substitute for a fixed value when no value is defined then replace the variable with an expression like one of these. e.g. you could do the same for the a variable in the first expression.

Tim

Evert

Joined: 15 Feb 2008
Posts: 174

View user's profile

Back to top
Link to postPosted: Tue Oct 18, 2011 6:30 pmPost subject: Reply with quote

Hi Tim,

Your explanation was pretty clear to me, but I can't get it to work. I'm trying to apply this method to keep track how much material is left in compound screening plates. So I have a table with well positions and plated amounts, and a child table where I enter a row each time a sample is taken from a specific well. The sample amounts are the summed in a Calculated field in the plate table. This works as long as there is no empty value for a sample (row 7). I tried to apply your code example, but without success so far, empty values for sample are not replaced by zero. Can you spot what's wrong?

Thanks,

Evert




 Filename: groovy.PNG    Filesize: 57.87 KB    Viewed: 13051 Time(s)
 Description:  
groovy.PNG
Evert

Joined: 15 Feb 2008
Posts: 174

View user's profile

Back to top
Link to postPosted: Tue Oct 18, 2011 6:54 pmPost subject: Reply with quote

Tim,

I think my problem resides in well 7 not being sampled in the sample table, rather than the sampled volume being empty (you still following me?). I need a method that supports this behaviour, since not all wells are always being sampled when running an assay (cherry picking). Obviously I could add an entry with empty sample value for all wells first, and then once specific wells get sampled, add these as well. But this is not very elegant...

Do you have a possible alternative solution?

I hope I made clear what I want to achieve.

Thanks,

Evert

Tim
ChemAxon personnel
Joined: 05 Oct 2004
Posts: 1703

View user's profile

Back to top
Link to postPosted: Wed Oct 19, 2011 3:12 pmPost subject: Reply with quote

Ahh! I think you mean that for row 7 there are no related rows in the child table?

If so then yes, this does seem to be a problem. It looks to me that this is causing a problem and the calculation is never being performed in this case. Probably some error is happening in this special case. We'll need to look into this further and fix it.

Tim

Evert

Joined: 15 Feb 2008
Posts: 174

View user's profile

Back to top
Link to postPosted: Wed Oct 19, 2011 4:02 pmPost subject: Reply with quote

That's correct, for row 7 there is no related row in the child table.

It would be great if the use of Calculated Fields in this way would tolerate missing child entries, and interpret them as being empty (null).

Thanks,

Evert

Tim
ChemAxon personnel
Joined: 05 Oct 2004
Posts: 1703

View user's profile

Back to top
Link to postPosted: Wed Oct 19, 2011 8:50 pmPost subject: Reply with quote

Yes, we'll improve this. Probaby sum will be zero, but things like min, max, avg will be null, and list will be an empty list.

Tim

fzimandl

Joined: 23 Jun 2011
Posts: 434

View user's profile

Back to top
Link to postPosted: Sat May 18, 2013 4:50 pmPost subject: Reply with quote

This was improved in 5.12.2 and also in forthcoming IJC 6.0 version.

Filip

Evert

Joined: 02 May 2013
Posts: 162

View user's profile

Back to top
Link to postPosted: Mon Jun 24, 2013 12:21 pmPost subject: Reply with quote

Hi,

I wondered if it is possible to create a Calculated Field for one database field, depending on the value of another field from the same table.

In the Wombat Demo database you have provided a Calculated Field coded example as follows:

def s = ''
each(type, value, target) { a,b,c ->
  s += "$a [$c] $b\n"  
}
s

Is it possible to build in a condition, for example the BIO.SPECIES field from the FK_ACT_LIST_ROOT entity has to be equal to 'rat', otherwise no value should be reported.

I hope you understand what I want to accomplish.

Thanks,

Evert

fzimandl

Joined: 23 Jun 2011
Posts: 434

View user's profile

Back to top
Link to postPosted: Tue Jun 25, 2013 5:58 amPost subject: Reply with quote

Hi Evert,

I'm not sure if I understand what should be the result.

Surely you can type a condition like "if (bioSpecies != 'rat') return null". But it's not the whole story, right?

Filip

Evert

Joined: 02 May 2013
Posts: 162

View user's profile

Back to top
Link to postPosted: Tue Jun 25, 2013 9:43 amPost subject: Reply with quote

Hi,

Maybe I can clarify this with my own data. I have metabolic stability data that looks something like this:

Compound Species Clearance
ABC123 Mouse 100
ABC123 Human 50
DEF456 Mouse 75

What I would like to do is to compose a separate Entity summarizing data for each compound in columns rather than rows:

Compound Clearance Mouse Clearance Human
ABC123 100 50
DEF456 75

I think this could be done through separate Calculated Fields, where the Species from the upper table is set as a condition for the reported Clearance.

Hopefully this clarifies what I want better. If so, could you provide me with the Groovy syntax for the Calculated Fields?

Thanks!

fzimandl

Joined: 23 Jun 2011
Posts: 434

View user's profile

Back to top
Link to postPosted: Wed Jun 26, 2013 8:09 amPost subject: Reply with quote

Hi,

Creating a separate entity with the calculated fields as you describe can not be achieved.

However it is possible to do the summarization in the same entity quite easily. Just defining calculated fields for Clearance Mouse, Clearance Human etc… I don't know if this is feasible way for you?

Another possibility would be to use SQL Entity. That way one can define proper SQL statement which pivots and summarize the data as you described. If you interested refer to https://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/editing_database/editing_entities.html#settings.

 

Filip

Evert

Joined: 02 May 2013
Posts: 162

View user's profile

Back to top
Link to postPosted: Wed Jun 26, 2013 8:56 amPost subject: Reply with quote

Hi,

what would be the synthax for the Calculated Fields within the same entity?

Thx,

Evert

fzimandl

Joined: 23 Jun 2011
Posts: 434

View user's profile

Back to top
Link to postPosted: Wed Jun 26, 2013 1:03 pmPost subject: Reply with quote

Hi Evert,

I realize that it did not return a wanted result. I was thinking about creating a new calculated field "Clearance Mouse":

if  (Species == "Mouse") {
return Clearance;
}

and the same could be done for other species. But it will not summarize it in one row.
There could be a workaround if you only need to pivot it "statically".

The scenario would look like:
1.) Having this multiple rows duplicated like this:

Compound Clearance Mouse Clearance Human
ABC123 100
ABC123 50
DEF456 75

2.) Exporting this to a file
3.) Importing the file into a new entity while there is set unique on Compound field (this may be tricky on text field, but it's easy on structure field)
4.) Those rows which were not imported correctly will be in an _error file (this will be written in import dialog)
5.) Importing error file, but merging fields in third step of Import file into existing entity can produce the wanted result (steps 4 and 5 may be necessary to repeat more times)

As you can see this is only a costly workaround (let me know if you are trying it and have some glitches). We don't provide simple pivot function at the moment. Though you may be interested in reading this blog post:
http://www.chemaxon.com/blog/code-class-utilizing-event-hooks-in-instant-jchem-for-drill-down-and-data-pivots/ 

Thanks,

Filip

Grigory

Joined: 15 Feb 2012
Posts: 3

View user's profile

Back to top
Link to postPosted: Wed Aug 21, 2013 3:45 pmPost subject: min max and other functions Reply with quote

Just to follow up on the calculated fields, since the simple functions like min or max are not yet implemented in the current IJC version (I have 6.0.3), could you provide groovy scripts for that?

Thank you in advance

Grigory

Joined: 15 Feb 2012
Posts: 3

View user's profile

Back to top
Link to postPosted: Wed Aug 21, 2013 4:44 pmPost subject: min max and other functions Reply with quote

Just found an apparently simple solution:

set variable type to "list" to return all values as a comma- and space-seprated list in square brackets, then set the expression to "value.max()". Appears to work just fine.

Here is a page I used for reference: http://groovy.codehaus.org/Quick+Start

Grigory

This topic is locked: you cannot edit posts or make replies.
Page 1 of 1


To watch this topic for replies   Register (enables digests) or give email address  
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum