Introduction
Many fields come directly from the data sources. Some fields such as aggregation of data are calculated using corresponding SQL constructs such as GROUP BY. However, there is also a need for many on-the-fly computation of row-level calculations. For example, the data source may contain the weight and height of a person and someone might want to view the BMI (Body Mass Index) for each person in addition to the weight and height fields. This is where calculated fields will come handy.
Excel-like
SQL Frames allows the full power of JavaScript to be able to create complex calculated fields. However, it also provides a way to create and use simple Excel-like functions to express the calculated fields.
For example, the BMI could be computed using
[weight]/([height]*[height])
Or, given the height and base of a right angle triange, the hypotenuse can be computed using
SQRT([height]*[height]+[base]*[base])
Valid SQL
Given SQL Frames tries to generate SQL based on the data transformation logic of the DataFrame created using SQL constructs, it is important to realize that not all calculated fields can be translated into equivalent SQL code.
At the moment, SQL Frames generates SQL from all fields which might result in invalid SQL. This is an area that needs to be further researched to provide the ability to distribute the computation between the server and client by computing some parts of the data transformation on the server (if required) and the rest on the client. However, there will be certain limitations with this approach. For example, while client-side calculation is fine to display the values, it won't be possible to sort by calculated fields which don't have equivalent SQL.
Note that this is a concern only if SQL Frames is being leveraged to do computation both on the server (using the generated SQL) and the client. For smaller data sets where the entire computation happens directly within the browser, this is not a concern.
Script Calculations
It is possible to introduce fields based on calculations using the SQL.script API.
Note that the script used is any valid JavaScript code. As a result, not all calculations
will translate correctly into a valid SQL. If the goal is to create valid SQL expressions
then it is better to make use of the various (although limited) functions listed in the SQL section.
Below is a simple example of creating script based calculated fields.
Just like in Excel, it is possible to create new calculated fields that refer to existing calculated fields as long there is no circular dependency. See nested calculated fields for more details.
Inline Calculations
There is need for calculations in many places such as for ordering or grouping the data. It is not necessary to define calculated fields first and then use them for sorting and grouping. It is possible to directly use calculations similar to other data fields in many places. This is convinient for coding but may not be suitable for no-code config. Not all no-code config widgets can handle such inline calculations at this time.
Calculation Field Editor
The Table -> Calculated Field menu option of a DataFrame shows all the existing calculated fields of that DataFrame and also allows defining new calculated fields. In addition, as the user is constructing the logic for the calculated field, a preview of the data, compiled code and any compilation errors are all displayed together. This should hopefully make it easy to create simple formulas and especially the data preview should help low-code developers to easily understand and debug their formulas.