Custom fields allow you to add extra columns for table and view data, but also on top of aggregated data. These fields can be defined as SQL formulas and depend on the SQL-flavor of the data source you are connected to. You can define any type of expression that is supported by the query language in
select statements. Some examples are:
a + b, where
bare the names of existing columns of the table
case when year([date_start]) = 2017 then [earnings] * [inflation_rate] else [earnings] end, where
inflation_rateare columns of the table residing in a SqlServer data source
concat("First name", ' ', "Last name"), where
Last nameare some table columns from a PostgreSQL database
Creating a custom field
To add a custom field, click on the Custom fields button from the header menu of table widgets. When the modal opens, click the plus button and introduce the desired name for this new custom field. Introduce the SQL formula in the text area below.
After such fields are added, the ScaiPlatform treats them as regular columns. All the available operations for regular columns are also available on custom fields, i.e. you can filter, order, aggregate, use the fields in graphs, etc.
Make sure that any column names used in the custom field definitions are properly quoted, according to the particular guidelines of the SQL data source you are exploring, e.g. `column name` for MySQL or *[column name]* for SqlServer
Custom fields can be defined only based on input columns. Chained custom field definitions are not supported.
Example: you cannot define a first custom field
concat([first_name], ' ', [last_name])and then a second field
concat('Mr. ', [a]). To avoid this limitation, you can define
concat('Mr. ', [first_name], ' ', [last_name])in our simple example, i.e. use only the base columns in custom field definitions.
Custom fields can only contain functions and expressions that do not perform any aggregation, but merely add extra columns to a table. Thus, you cannot use aggregate functions like
max, etc. in custom field definitions.
Custom fields are restricted to source owners. Since custom fields allow the user to execute arbitrary SQL statements on the data source, for security reasons, only source owners are allowed to create these custom fields. If you do not see the button in the header menu of the table widgets, it means that you are not the owner of the data source and cannot add custom fields.