Certain in-memory data functions require you to provide a special type of expression. These are applicable in the following circumstances:
- Derived Expression Columns - When creating an in-memory column of the type Derived Expression, you need to provide the expression that will calculate the value it should display.
- Filter Expression - Several in-memory functions—such as filtering, updating, and removing rows—require you to provide a filter expression that determines which rows will be affected.
Because these expressions are specific to working with in-memory data, they require slightly different syntax from a standard expression. To start, they can include the names of columns in the data source, as well as operators and literal strings.
Column names can be written as a normal keyword, with no additional characters needed. For example, if you want to multiply one column's value with another, you could use the following expression:
FirstColumn * SecondColumn
The only exceptions to this is if the column name contains any non-alphanumerical characters (such as punctuation), or if it contains any of the following words:
- And
- Between
- Child
- False
- In
- Is
- Like
- Not
- Null
- Or
- Parent
- True
This is because these match built-in KnowledgeKube functions, which would also confuse the parser. In order to work with column names that contain these words, you would need to wrap them either in square brackets ( [ ] ) or grave accents ( ` ). For example, if you want to reference a column with the name "Order#" in an expression, you could write it like this:
[Order#]
Or you could write it like this:
`Order#`
In addition to column names, you can also include string values by surrounding them by single quotation marks ( ' ). As such, if you want to compare a column value with a literal string, you could use the following:
ColumnName = 'This is a string'
Normally, you would enclose strings within double quotation marks, but this does not work here since these expressions are nested within another, which would confuse the expression parser. As such, make sure you use the right character for the right situation.
Date strings are slightly different in that they can also be enclosed within hash signs ( # ), depending on the data provider. In this case, you would write the expression like this:
BirthDate < #01/31/1982#
Apart from literal string values and column names, filter expressions can also contain keyword values, but this requires you to write the filter condition within a variable first. You could do this using simple concatenation, or the FormatString function.
For example, if you have a data source column called Colour and want to filter it based on the current value of the variable SelectedColour, you could use the following expression:
"Colour='" & SelectedColour & "'";
You could then assign the value of this expression to a variable, which you can then specify as the filter condition in the expression. In this instance, if the current value of SelectedColour is “Green”, this will generate the following string, which is a valid filter condition:
Colour='Green'
This means that if this was used as the filter, the expression will affect all rows in the data source where the value in the Colour column equals “Green”.