script for data treatment

Hello!

Please help me write a script that will do the following:

Each compound from the database has a table with suppliers, this table contains fields: SUPPLIER, WEIGHT, PRICE_UNIT, PRICE. I need to the table with a structures add the prices of the best suppliers into  the next fields: SUPPLIER  1 G, 1 G PRICE, SUPPLIER 5 G, 5 G PRICE, SUPPLIER 10 G, 10 G PRICE. List of good suppliers is in the text file on a disk. Suppliers in this file are sorted - the first is the best and the last is worse. If the compound has not the supplier from this list, the fields do not fill. Prices of suppliers should be given in the EUR. ie if price_unit is USD than shoved price = price from the table of supplier / 1.35, or if price_unit is CNY than shoved price = price from the table of supplier / 0.12.

in Appendix attached the data base where for the first compound is an example of required filling of the fields.

Thanks!

Igor

Hi Igorlab,

We are currenty considering your requirement...two immedidate questions from me :

You stated that there is a effectively an "available_amounts" table per molecule row in your structures table ?

How many structures do you have in your main molecules table, thus how many such tables, how are they named ?

Is it easier if your list of preferred suppliers order is also in a database table with a rank order column rather than in an external text file ?

Thanks,

Daniel.

Perhaps it is possible for you to provide a small or simple ER diagram of your entity / arrangements ?

Hi Daniel,  

Usually, in molecules table not more then 2000 structures, and usually there is one table like in base, sent before.  List of good suppliers can be added directly into the script code. May be, my description to You about my table isn't good, but You can see it in the "Form view" in attached example DB.  The field WEIGHT is in the table RDF_SUPPLIERS.  

Thanks!

Hi Igorlab,

OK - can you please send your project as a zip file rather than .rar...I open the rar and tried to turn into zip but it not import OK. You should be able to right click on the schema node and choose "back up to zip".

Thanks,

Daniel.

All project folder is in the zip file Example.zip.

backuped schema is in the file Schema.zip

Thanks,

Igor

Hi Igorlab,

We have found a starting point way to approach this requirement using calculated fields.

supplier_1g

def prefs = ['SIG','CHB','ASI'] // your order list
for (String p : prefs)
{int i = 0;
 assert p instanceof String
 for (String s : supplier)
{String w = weight
 assert s instanceof String
 if (s.equals(p) && w.equals('1'))
 {return p;}
 i++;
}} 
return null;

def prefs = ['SIG','CHB','ASI'] // your order list
for (String p : prefs)
{int i = 0;
assert p instanceof String
for (String s : supplier)
{String w =  weight
 String u = priceUnit 
 String r = price 
 assert s instanceof String
 if (s.equals(p) && w.equals('1'))
 { // do what ever conversions etc...
 return r;
 }
 i++;
}} 
return null;

We did not inlcude any currency conversion - we leave you to that pleasure !

Happy 400th CXN post to me !!!

thank you so much for the quick response!

but the implementation of your script creates a new field but it is not filled

Steps I did:

- Added calculated field

- Added two variables - wheight & supplier and chenge their type to LIST

- Added your code and add "def prefs = ['ALDRICH', 'ACROS', 'ABCR', 'ACC', 'ALFA'] / / your order list"

- Validate and apply - field appearing but it is empty for all record

I'll deal with conversion rates, thanks again!

I was concerned about what others in your database table names, besides, I do not see a table separately with suppliers - you have it, as I understand it, is in the data table "Data".  I renamed my table according to your.

apparently, you created a project for another way - I just imported the RDF file into an empty project.

Thanks!

Igor

Hi,

To coin a phrase : "That will never do".

Please can you send me the schema as it is now in zip form ?

Perhaps you can send me directly to : dbutler _at_ chemaxon.com

I can then try and rationalise why you see this behaviour, amend and send back...

Cheers,

Daniel.

Hi Daniel,

Your script partly working after some correction, if /*&& w.equals('1')*/ is not "commented", script doesn't work.

Thanks,

Igor

p.s. I have sent the schema to your mail.

def prefs = ['ALDRICH', 'ACROS', 'ABCR', 'ACC', 'ALFA']  // your order list
for (String p : prefs)
{int i = 0;
 assert p instanceof String
 for (String s : supplier)
{
String w = weight
assert s instanceof String
 if (s.equals(p) /*&& w.equals('1')*/)
 {return s + " " + w;}
 i++;
} 
} 

Please try adding under 

assert s instanceof String

assert w instanceof String

uncomment the w.equals and try again - this bit is needed to work as expected of your stated requirement.

if still problem, please feel free to do as mention above and send over zip direct to me.

This pair seem to work for me...

def prefs = ['ASI','SIG','CHB'] // your order list

for (String p : prefs)

{int i = 0;

 assert p instanceof String

 for (String s : supplier)

{String w = weight

 assert s instanceof String

 assert w instanceof String

 if (s.equals(p) && w.equals('1'))

 {return p;}

 i++;

}} 

return null;

def prefs = ['ASI','SIG','CHB'] // your order list

for (String p : prefs)

{int i = 0;

assert p instanceof String

for (String s : supplier)

{String w =  weight

 String u = priceUnit 

 String r = price 

 assert s instanceof String

 assert w instanceof String

 if (s.equals(p) && w.equals('1'))

 { // do what ever conversions etc...

 return r;

 }

 i++;

}} 

return null;

it scripts doesn't work...  

the steps that I follow:

 - create empty project  

-clik on the localdb  tools/chemistry/RDF import (chose the file rdf2.rdf, import using existing template and clik Next, )

 -then add new view - table  

-add calculated field with your code but it doesnt work...

Igorlab,

I have taken the rdf you supplied and processed it.

I have created two calculated fields and used the code I suppllied.

I edited them in terms of the hard coded values that actually exist in "RDF2_SUPPLIERS" table.

Such an example is supplier = 'TCI-US' & weight = '500 G' which returns correctly price 332.20.

My suggestion was a pair of these calculated fields for each weight class.

There are entry for '1 G', '5 G' and '10 G' as well as others - too many calculated fields can cause performance degredation. There are many different suppliers listed.

The point is you need to edit these expressions to reflect your own data?

[If you cannot see it, create a separate supporting data tree for "RDF2_SUPPLIER" - see screenshot]

dsb

Daniel,

It is amazing! but I have all of the cells remain unfilled ...

What am I doing wrong?

Igor

I think you have to use the data that exist in SUPPLIER and WEIGHT fields in your hard coded preferred list

and weight class. I can see in your screenshot that you reference items that do not exist in the table - of course, that cannot work ?

def prefs = ['TCI-US'] // your order list

for (String p : prefs)

{int i = 0;

 assert p instanceof String

 for (String s : supplier)

{String w = weight

 assert s instanceof String

 assert w instanceof String

 if (s.equals(p) && w.equals('500 G'))

 {return p;}

 i++;

}} 

return null;



def prefs = ['TCI-US'] // your order list

for (String p : prefs)

{int i = 0;

assert p instanceof String

for (String s : supplier)

{String w =  weight

 String u = priceUnit 

 String r = price 

 assert s instanceof String

 assert w instanceof String

 if (s.equals(p) && w.equals('500 G'))

 { // do what ever conversions etc...

 return r;

 }

 i++;

}} 

return null;

I'm sorry, I'm not a programmer. In your code, I just changed the list of suppliers. The only thing that I used and it was not in the table is w.equals ('1 ') copied to your reply, but it turns out it was necessary to use w.equals ('1 G'). 

Now works, thanks!

Igor

Good news and so I will close this requirement as "fixed"...