Working with expressions
Use an expression to create a calculated field from one, or multiple fields in a database. You can create a simple expression that concatenates strings by joining several fields or a complex expression that uses mathematical operations, such as logarithms. For expressions generating a decimal field, you can specify precision up to six decimal places. For example, a typical calculated field can display customer orders based on the year in which each order was placed.
To further demonstrate, you can use the following equations to accomplish the listed tasks:
Concatenate Text (+): join two text fields.
[Demo].[Customer].[Title]+[Demo].[Customer].[Surname]
Concatenate (+): join two numeric fields or strings.
STRING([CustomerID])+5+[DaysSinceLastOrder]-1
Mathematical functions (+, -, *, /): numeric fields can be added, subtracted, multiplied, or divided.
STRING([Demo].[Customer].[Cust_ID]+[DEMO].[Customer].[Household_ID])
Boolean values: (logic 1, logic 0) assignment of Boolean values to fields, based on the conditions that are established.
[Demo].[Customer].[Edats]EQ"Adults">0 or [Demo].[Customer].[Edats]EQ"Joves">0
Constants: a constant is a fixed value; in this case a constant function is one that gives a single value for a set of variables.
[Demo].[Customer].[Household_ID]=1
This expression returns the value to position 1 in the Household_id field.
Switch statement: checks the content of a field and, depending on its value, replaces it with the specified value.
SWITCH([Demo].[Customer].[Gender])CASE"M":" MALE",CASE"F":"FEMALE"SWITCHEND
Conditional Boolean value (Boolean if statements): complex conditions can be tested, such as whether the customer’s age has a specific characteristic. Returns either true or false. Instead of returning true or false, the expression can return other values, such as yes or no.
If([DEMO].[Order].[OrderDate]>[Demo].[Customer].[DOB],1,0)
To display help for a function, double-click the function.
Figure 3‑13 shows the help for the AGE function.
Figure 3‑13 Help for AGE function
How to create an expression
1 In Enrichment—Engineering, choose Expressions.
2 From My Data, drag a destination table for the field you want to create.
3 Type a name for the field, using any characters except:
/ \ º ª - accents, dieresis,?, !, *, @, #, &, $, o, ñ
4 Drag a function from the left pane and drop it in the function editor. The function appears in the editor, followed by parentheses.
5 Drag a field name from the destination table and drop it in the parentheses.
6 If necessary, edit the expression syntax.
7 Choose Create. A new calculated field appears in the destination table.
Video tutorials
