Pivoting tutorial

For this example we will use two entities - one containing the assay data and one with the structural data. The resulting IJC schema of this tutorial is displayed below (Fig. 1) and the files to be imported can be downloaded here :

Pivot_Assay data.tab

Pivot_Molecules.sdf

Figure 1

Schema preparation

First, please create a new IJC schema with a local database as described here. Then you can create a structure entity called Structures and a standard entity called Assay data as described here in the documentation. Then, right click on the entity name and import the data to each of the entities - SDF file to the Structures and the TAB file to the Assay data. Please make sure you that you checked "First line is field names", in the first step of TAB file import (Fig. 2A) . Importing data into IJC is described here in the documentation or you can look into the tutorial. As soon as you have these two entities ready, you can bind them in a many-to-one relationship (Assay data Structure_ID are on the many side of the relationship and CdId in the Structures are on the one side) (Fig. 2B). The relationships are discussed here in the documentation or you can follow one of the other tutorials.

Figure 2A.

Figure 2B

The last preparation step will include the creation of a data tree called Complex data tree containing both Structures and Assay data entities as described here in the documentation or in the relational form tutorial with the Structures entity being the parent (Fig. 3).

Figure 3

Creating the Pivot entity

You can create the new pivot entity either using the right-click menu on the schema node in the projects window and the New Data Tree and Pivot entity (virtual view)... option (Fig. 4A) or using the schema editor, entities tab, where the right-click menu allows the selection of New Pivot entity (virtual view)... option (Fig. 4B).

Figures 4A (left), 4B (right)

In both cases, a New Pivot Entity window opens (Fig. 5). In this dialog, please define the display name ( Pivoted data ), select the original entity (Assay data) and row (Structure_ID), column (Assay concentration), value (Result 1, Result 2) and extra value (Result 3) fields as displayed on Fig. 5. You can also set the aggregation for value and extra value fields, we have used average, min and max.

Figure 5

A new entity is created. Using the Schema editor you can confirm that it was bound to the original entity by the default pivoting relationship which is described here in the documentation. In case you have created the pivot entity from the project window, a new data tree and a corresponding grid view are created automatically and the view is opened for you. It might look like the one below (Fig. 6).

Figure 6

Displaying pivoted data

You might have probably noticed that the query mode is not available in the view displayed above. One of the limitations of pivot entity is that it is not directly queryable. In this tutorial we will overcome this issue by using the default pivoting relationship and including the pivot entity in the Structures data tree we have created previously. Please navigate to the Schema editor, Data trees tab and right-click on the Structures data tree, so you can select Manage Child Entities… option (Fig 7A). In the following window please expand the Assay data node and select the checkbox next to the Pivoted data (Fig 7B).

Figures 4A (left), 4B (right)

This will allow us to add widgets displaying data from Pivoted data entity together with other data in views related to Structures data tree. After clicking Finish button, you can go back to the project window and using right-click menu on the data tree node, create a new form view. In this tutorial, we will create a form Structures and Assay data view as in the screenshot below (Fig. 8). It contains one molecule pane (bound automatically to the Structure field, Structures entity) and three table widgets - one just for displaying CdId field from the Structures entity one bound to the original Assay data entity (all fields) and one to the Pivoted data entity (all fields).

Figure 8

Such view can be utilized to query the original data and display the related pivoted values. In the screenshot below we have searched for the benzene substructure and Result 1 < 10. You can notice that the CdId selection allows you to compare the pivoted data for all three structures (Fig. 9).

Figure 9

Congratulations

Congratulations! You have just created a form displaying pivoted data by learning :

• How to create a pivot entity

• How to include the pivoted entity in a data tree with the original entity

• How to build a view displaying both original and pivoted data