You can type CLEM expressions manually or use the Expression Builder, which displays a complete list of CLEM functions and operators as well as data fields from the current flow, allowing you to quickly build expressions without memorizing the exact names of fields or functions.
The Expression Builder controls automatically add the proper quotes for fields and values, making it easier to create syntactically correct expressions.
- The Expression Builder is not supported in parameter settings.
- If you want to change your datasource, before changing the source you should check that the Expression Builder can still support the functions you have selected. Because not all databases support all functions, you may encounter an error if you run against a new datasource.
Opening the Expression Builder
The Expression Builder is available in all nodes where CLEM expressions are used, such as Select, Balance, Derive, Filler, Analysis, Report, and Table nodes. You can open it by double-clicking the node to open its properties, and then clicking the calculator icon:
The Expression Builder provides not only complete lists of fields, functions, and operators ‒ but also access to data values if your data is instantiated.
- Type in the Expression box, using the function and field lists as
Select the required fields and functions from the lists.
- Double-click to add the field or function to the Expression field.
- Use the operand buttons to insert the operations into the expression.
The function list displays all available CLEM functions and operators. Scroll to select a function from the list, or, for easier searching, use the drop-down list to display a subset of functions or operators. For details, see Selecting functions.
The field list displays all fields available at this point in the data stream. Scroll to select a field from the list. Double-click the field to add it to your expression. You can also select from recently used fields.
Viewing and selecting field values
You can view field values in the Expression Builder. Note that data must be fully instantiated in an import or Type node to use this feature, so that storage, types, and values are known.
To view values for a field from the Expression Builder, select the required field to list its values. You can then insert the value into the current expression or list.
For flag and nominal fields, all defined values are listed. For continuous (numeric range) fields, the minimum and maximum values are displayed.