Pivot Table Entity

A new type of entity was introduced in 16.10.3.0 version of Instant JChem - the pivot entity. It is a virtual data view which can pivot data from an original entity (both structure and standard entities can be source of data).

A pivot table is a data summarization tool which is a usual part of data visualization packages. It can display various data sorted into different ‘categories’. Generally, a pivot table can be presented as seen in the following figure (Fig. 1) .

Figure 1

To be able to create the pivot table in IJC, you need to have the entity containing the following data present in your IJC schema/database (Fig. 2). In order to clarify the use of different values in the original table, we have used four colors for highlighting.

Figure 2

During pivot table creation it is possible to select the fields from the original entity and assign their roles in pivoting:

Row - the row field values identify a row in the pivot table (blue in the figures)

Column - the columns are created based on the values present in this field (red in the figures)

Value - the data values are displayed for each combination of row and column values, can be aggregated (green in the figures)

Extra value - the data values for the matching row value, can be aggregated (yellow in the figures)

In case your database contains multiple data entries for one combination of ROW data entry and COLUMN data entry, it is possible to display all the values separately, or use one of the four aggregation functions available (sum, average, min, max).

Creating a pivot entity

A new pivot entity can be created 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. 3A) or using the schema editor, entities tab, where the right-click menu allows the selection of New Pivot entity (virtual view)... option (Fig. 3B). In both cases, a New Pivot Entity window opens (Fig. 4).

Figure 3A Figure 3B

Figure 4

Using the New Pivot Entity window it is possible to define all the settings for the pivot entity:

Display Name - set a name to the pivot entity, the default name is Data

Original Entity - select the entity with data to be pivoted from the dropdown menu. Based on the selection of the Original Entity , all the fields and their types are loaded into the dialogue.

As Row - selected field from the Original Entity will be used as the row value; it is possible to use multiple row fields to create unique combinations to identify your data

As Column - selected field from the Original Entity will be used as the column value; it is possible to use multiple column fields

As Value - selected field from the Original Entity will be used as the value field; it is possible to use multiple value fields

As Extra Value - selected field from the Original Entity will be used as the extra value field; it is possible to use multiple extra value fields

Allow creation of empty fields - controls the creation of empty pivot fields (fields with no data)

Show empty rows - controls the visibility of empty rows

When a pivot entity is created, it is linked to the original entity by a default 1:N relationship which links all the rows in the original entity to the resulting row in the pivot entity.

Working with a pivot entity

The pivot data can be visualized using various types of widgets, but the most suitable are probably grid view or table widget.

Similar to other entities, changes to the pivot entity can be done using Schema Editor (Fig. 5). Using the entities tab, it is possible to change the Display Name and aggregation of value and extra value fields. Also, the order of value and extra value fields can be changed.

Figure 5

The data in the original database table might change in time - new values for column fields can be introduced or some values might not be used anymore and disappear from the table. The pivot entity can process these changes by refreshing. Any user with ROLE_EDIT_SCHEMA can refresh the pivot entity using Schema editor (Fig. 6A), which leads to creation of new fields in the pivot entity, or to disabling fields which are no longer used. The disabled fields are highlighted by a red badge in the schema editor (Fig. 6B). If the missing value is reintroduced to the original table in the future, the field is enabled again. This allows to avoid the issue with table columns and/or other widgets becoming unbound.

Figure 6A Figure 6B

Using the default relationship it is possible to include the pivot entity in the data trees which contain the original entity. This approach can facilitate the query feature on pivot entity. More information about creating and editing data trees can be found here in the documentation.

The pivoted data can also be exported to *.xlsx, *.csv and *.tab files (Fig. 7). For more details about export actions please look into the documentation.

Figure 7

We welcome feedback on this feature. Please provide the feedback via the Instant JChem forum.