Transform - Numbers: Expression

The Expression template appears in the template list when a numeric field of type float32, float64, int8, int16, int32, int64uint8, uint16, uint32, or uint6, has been picked in the Transform pane.  The template computes the specified SQL expression that evaluates to a numeric data type.

 

Expression

Save the numeric result of the given expression into the specified numeric Result destination, creating new fields using the specified numeric type.

 

Launch the template by choosing a numeric field and then double-clicking the Expression template.  When the template launches we can write an expression and specify options.

 

 

 

When we choose a numeric field and then launch the Expression template, we can write any SQL expression we like, so long as it evaluates to a numeric value, and the result will be saved into the specified Result destination.

 

The Expression template is usually used to perform calculations on multiple fields, so long as the result is numeric.   The initial field chosen when we launch the Expression template need not participate in the computation, and it need not be the Result destination: it is simply an initial placeholder used to choose the desired result data type for the expression.

 

We start with a table with a single Numbers field of type float64.   We have used the Layers pane to un-hide the mfd_id field, since we will use it in this example.   We will create a new field and populate it with the result of the Numbers field multiplied by a random number from 1 to 100 that we will create using the mfd_id field as a seed.

 

 This example shows how to create an expression that uses an SQL function.   That is fine for teaching how to use the Expression template, but in real life if we wanted to load a table with random numbers we would just use the Transform - Numbers: Random  template.

 

 

With the focus on the table window, in the Transform pane we choose the Numbers field and then the Expression template.

 

 

In the Expression template, we press the Edit Expression button.

 

 

The Expression dialog pops open, providing full query builder infrastructure, giving us access to all of SQL and hundreds of functions.

 

We enter the expression:

 

((TABLE CALL ValueSequenceRandomInt(1, 100, [mfd_id])) + 1) * [Numbers]

 

We have added extra line feeds and space characters so the entire expression fits into the size of illustrations used in this topic.  

 

The expression uses the  ValueSequenceRandomInt SQL function to create a random number from 0 to 99, using the mfd_id field as a seed for the random number generator.

 

Press OK.  

 

 

Back in the Transform pane,  for the Result destination, we choose New Field and then enter Random results as the name of the new field to add to the table.  We could use whatever name we want, but it is wise to use names that remind us of what they are supposed to be.   We leave the Result type at the default float64.

 

If we preferred, we could have chosen some other existing numeric field in the table as the Result destination.

 

Press Transform.  

 

 

The template creates a new float64 type field called Random results and populates it with the result of the expression we entered, a random number from 1 to 100 multiplied by the Numbers field.

Creating a Field and Filling it with a Value

In addition to evaluating SQL expressions of arbitrary complexity, the Expression template is a convenient way of doing simple things as well, such as simultaneously creating a new field and filling it with a desired value.

 

If we already have a numeric field in our table, it is quicker to simply enter the desired value into that field's cell for a given record, press Ctrl-A to Select All records, and to then right-click on the cell and from the context menu choose Copy to Selection. That will fill the cells for all selected records (all of them) with the desired value.

 

However, if we have not yet created the destination field we can create the field and fill it at the same time using the Expression template.   In the following example we will create a new field and then fill it with pi.  

 

We start with a table with a single Numbers field of type float64.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Transform pane we choose the Numbers field and then the Expression template.

 

 

In the Expression template, we press the Edit Expression button.

 

 

In the Expression dialog that pops open, we enter the number 3.1416 and press OK.  

 

The Expression dialog provides the full query builder infrastructure, giving us access to all of SQL and hundreds of functions, but in this case we will use it to just enter a single number.

 

Back in the Transform pane,  for the Result destination, we choose New Field and then enter Pi as the name of the new field to add to the table.  We could use whatever name we want, but it is wise to use names that remind us of what they are supposed to be.   We leave the Result type at the default float64.

 

If we preferred, we could have chosen some other existing numeric field in the table as the Result destination, and thus filled that field with 3.1416.

 

Press Transform.  

 

 

The template creates a new float64 type field called Pi and populates it with the result of the expression we entered, in this case a very simple expression consisting of a single number.

 

If we wanted to be more precise in terms of a value of pi, we could use the constant that is built into Manifold SQL.

 

 

In the Transform pane, we press Edit Expression again, and in the Expression dialog that pops open we enter PI for the expression.  Press OK to get back to the Transform pane.

 

Back in the Transform pane, for the Result destination, from the pull down menu we choose the Pi field we created in the previous run.      

 

Press Transform.

 

 

Much better!   The Pi field is immediately updated with the more precise value for pi that is used for the built-in Manifold SQL constant.

 

 

See Also

Transform Pane

 

Transform Reference

 

Transform - Expression

 

Transform - Numeric Vectors

 

Transform - Numbers

 

Transform - Numbers: Arithmetic

 

Transform - Numbers: Bit Logic

 

Transform - Numbers: Copy

 

Transform - Numbers: Hyperbolic

 

Transform - Numbers: Limit

 

Transform - Numbers: Random

 

Transform - Numbers: Round

 

Transform - Numbers: Special

 

Transform - Numbers: Trigonometric