# Calculated Field helper functions

User e05b1833aa

16-10-2011 18:35:33

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

ChemAxon fa971619eb

16-10-2011 19:16:29

```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

User e05b1833aa

17-10-2011 08:08:42

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

ChemAxon fa971619eb

18-10-2011 17:02:00

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

User e05b1833aa

18-10-2011 17:30:33

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

User e05b1833aa

18-10-2011 17:54:15

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

ChemAxon fa971619eb

19-10-2011 14:12:30

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

User e05b1833aa

19-10-2011 15:02:51

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

ChemAxon fa971619eb

19-10-2011 19:50:25

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

ChemAxon 2bdd02d1e5

18-05-2013 15:50:50

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

Filip

User 8a7878ec6d

24-06-2013 11:21:16

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

ChemAxon 2bdd02d1e5

25-06-2013 04:58:28

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

User 8a7878ec6d

25-06-2013 08:43:59

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!

ChemAxon 2bdd02d1e5

26-06-2013 07:09:44

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

User 8a7878ec6d

26-06-2013 07:56:40

Hi,

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

Thx,

Evert

ChemAxon 2bdd02d1e5

26-06-2013 12:03:02

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

User 283813f0a1

21-08-2013 14:45:09

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

User 283813f0a1

21-08-2013 15:44:07

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