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