Transform - Expression

Preliminary topic - to be updated.   Transform templates appear for fields of various types that allow use of an SQL expression to transform data.   Choosing a field in the Transform pane chooses the data type for the expression.  The system will parse expressions that are written and will pop open an error dialog if the expression evaluates to a data type other than the chosen data type.

 

Click the Edit Expression button to write a new expression or to edit an existing expression that has persisted from a prior use of the transform template.   The Edit Expression button launches an Expression dialog that facilitates writing an expression with Query Builder features.  Expressions can be used like transform templates, allowing the result to be saved to the same field, to a new field of the chosen type, or to a new table.    

 

Expressions generate results using the data type of whatever field was chosen, but any field or data type can be used in the expression.   If we change our mind about the data type of the results we intend to create with the expression, we can press the Up button in the Transform pane to return to the top level, where a new field of the desired type can be chosen.

 

SQL expressions can be used in the Transform pane either in the Expression template or to provide a value for some option box where the value is the result of an expression.  Exactly the same Expression dialog is used in both cases to help build the expression.

 

 

In the illustration at left above we have chosen the Expression template.   Pressing the Edit Expression button will launch the Expression dialog, allowing us to compose a new expression or to edit an existing expression that may already be in the pane.

 

In the illustration at right above, we have chosen the Expression option to provide a value for parameter that is used by the Reshape template's shift operation.    Choosing the Expression option also launches the Expression dialog, allowing us to compose a new expression or to edit an existing expression that may already be in that parameter box.

The Expression Dialog

Click the Edit Expression button to launch the Expression dialog, to enter a new expression or to edit an existing expression.   The Expression dialog facilitates writing an expression with Query Builder features.  

 

 

Expression

Write an SQL expression into this pane, either manually or assisted by the expression builder.

Filter

Enter text to be matched, case not significant.   The list of fields,  operators, and functions will be reduced to show only those items which match the text.  For example, entering null reduces the list to only those items that have NULL in their names.

Fields, Operators, Functions

Lists of fields and their types from the table used by the component that is the subject of the Transform pane, along with lists of available SQL operators and functions that may be used in the expression.

Data type

The data type of the targeted field or parameter to which the expression must evaluate.   For example, if the targeted field is a text field, the expression we write must evaluate to a text value.   Writing an expression which does not evaluate to the indicated data type will cause an error message to pop open when we press OK.

 

The Expression dialog provides two panes within the dialog: an upper pane in which we can write expressions, and a lower pane that shows fields in the table, operators, and functions we can use.  Just as with the query builder in the Command Window, double-clicking on a field or an operator or function will add it to the expression we are building.   Expressions must evaluate to a boolean result to be used for selections.

 

When we click the OK button to apply edits to an expression, the dialog automatically checks the syntax of the entered expression and pops open an error message on any error.  The dialog also automatically checks the type of the entered expression and pops open an error message if the type is incompatible with the expected type. The expected type for the expression is shown in the left bottom corner of the dialog.

 

The same Expression dialog is used to write expressions used in parameter boxes.   Expressions shown in parameter boxes are automatically compressed on the fly to maximize room of available display space, which tends to be limited in value boxes.  Comments and unnecessary whitespace are removed, multiple lines are squeezed into a single line, and so forth.  Opening the expression will show the original form, with comments and whitespace restored.  Expressions that consist of multiple terms are also enclosed in parentheses ( ) to enforce computation order in the query text.

Example

Suppose we have a table showing products in inventory, with the number of units in stock, the number of units on order, the price for each unit and a discount applied to the price.  We would like to compute the discounted value of the inventory in stock and to put that value into a new field in the table.

 

 

We begin by launching the Expression template using a choice of field that we know is the same general data type, in this case a float64 type, the Stock field.   We want our expression to compute a float64 result, so we can use Stock to start as long as it is any numeric data type, such as an int32 or other numeric type, that can be converted on the fly into the desired float64 output.

 

 

In the example above, we have chosen the Stock field in our Products table, because we know it to be a float64 data type.  If we did not know the types of fields in the drawing's table, we could open the table and choose Show Type in the filter button for the Layers pane to show the data type for each field.   

 

Double-clicking the Expression template to launch it, we see Stock cited as the active field.  As the active field, it sets the data type expected for the result of the expression, and it will be the destination for the Result if we leave (same field) in the Result box.    

 

We press Edit Expression.

 

 

We enter the expression:

 

RoundDecs([Stock] * [Price] * (1 - [Discount]), 2)

 

That is simply the arithmetic computation for the discounted value of inventory in stock:

 

[Stock] * [Price] * (1 - [Discount])

 

that is wrapped within a use of the RoundDecs function to trim the number of decimal digits to only the first two after the decimal point.   The Expression pane allows us to use whitespace to format our text to make it more legible, for example, to enable all parts of the expression to be visible.

 

Note that the Expression dialog shows the data type of the intended result in the lower left corner.  If we write an expression that does not parse to a compatible type (for example, resulting in a text type instead of a numeric type such as int32 or float64) the system will pop open an error message.

 

Press OK.

 

 

Back in the Transform pane, we change the Result to creating a new field, and we enter Discounted Inventory Value as the name of the new field.  If we wanted, we could change the Result type to any numeric value.  We leave it at float64.  

 

Press Transform.

 

 

Immediately, a new field called Discounted Inventory Value appears in the table, and it is populated with the results of the expression.  Done!

 

The first two records have only one digit after the decimal point because the next digit in the value generated by the RoundDecs rounding is zero in both cases, so Manifold does not print the non-significant zero at the end of the decimal fraction.

Example

Suppose we have a table that for each record has two geometry fields, both with geometry for a point:

 

 

The points show the position of monitoring stations for the years 2010 and 2020.  Monitoring stations may have moved due to agricultural activities, so we would like to know the shift in X direction and the shift in Y direction from 2010 to 2020 for each station.   To do that, we will use fields that are type geom but we would like to save the results to fields that are type float64, ideally asking the Transform pane to create those fields for us.

 

We begin by launching the Expression template using a choice of field that we know is a float64 type, the SQKM field.

 

 

In the example above, we have chosen the SQKM field in our Mexico table, because we know it to be a float64 data type.  If we did not know the types of fields in the table, we could choose Show Type in the filter button for the Layers pane to show the data type for each field in the table.  

 

Double-clicking the Expression template to launch it, we see SQKM cited as the active field.  As the active field, it sets the data type expected for the result of the expression, and it will be the destination for the Result if we leave (same field) in the Result box.    

 

We press Edit Expression.

 

 

In the Expression dialog, We enter the following expression:

 

VectorValue(GeomCoordXY([Geom2020],0),0) -

   VectorValue(GeomCoordXY([Geom2010],0),0)

 

That expression extracts the X value from the Geom2010 field and subtracts it from the X value extracted from the Geom2020 field, using an expression very similar to that used in the Example: Create a Geocoded Table from a Drawing topic.

 

Press OK.

 

 

Back in the Transform pane, we change the Result to creating a new field, and we enter Delta X as the name of the new field.  

 

Press Transform.

 

 

Immediately, a new field called Delta X appears in the table, and it is populated with the results of the expression.  

 

To compute a change in Y values, we press Edit Expression again to change the expression slightly.    

 

In the Expression dialog (not illustrated), we change the expression to be:

 

VectorValue(GeomCoordXY([Geom2020],0),1) -

   VectorValue(GeomCoordXY([Geom2010],0),1)

 

That expression extracts the Y value from the Geom2010 field and subtracts it from the Y value extracted from the Geom2020 field.

 

We press OK to get back to the Transform pane.

 

 

Back in the Transform pane, we enter Delta Y as the name of the new field.  

 

Press Transform.

 

 

Immediately, a new field called Delta Y appears in the table, and it is populated with the results of the expression.  Done!

 

See Also

Transform Pane

 

Transform Reference

 

Transform - Binary

 

Transform - Boolean

 

Transform - Datetime

 

Transform - Geometry

 

Transform - Numbers

 

Transform - Numeric Vectors

 

Transform - Text

 

Transform - Tiles

 

Transform - UUID