Formula

Properties that can be configured for the Formula Preparation Component.

Syntax Use this component to apply predefined functions and operators on the data. All functions and expressions except data manipulation functions add a new column with the formula result.
Note When entering a string literal that contains single quotation marks, each single quotation mark inside the string literal must be escaped with a backslash character. For example, enter 'Customer's' as 'Customer\'s'.
Note When entering a column name that contains square brackets, each square bracket inside the column name must be escaped with a backslash character. For example, enter [Customer[Age]] as [Customer\[Age\]].
Formula Properties
Table 1: Data Preparation Component Properties
Property Description
Formula Name Enter a name for the new column created by applying the formula.
Expression Enter the formula you want to apply. For example, Average([Age]).

Example:

Calculating average age of employees
Employee Table:
Emp ID Emp Name DOB Age Date of Joining Date of Confirmation
1 Laura 11/11/1986 25 12/9/2005 27/11/2005
2 Desy 12/5/1981 30 24/6/2000 10/7/2000
3 Alex 30/5/1978 33 10/10/1998 24/12/1998
4 John 6/6/1979 32 2/12/1999 20/12/1999
To calculate average age of employees, perform the following steps:
  1. Drag the Formula component onto the analysis editor.
  2. In the properties view, enter a name for the formula.

    For example, Average_Age.

  3. In the Expression field, enter the formula: AVERAGE([Age])
  4. Choose Validate to validate the formula syntax.
  5. Choose Done.
Output table:
Emp ID Emp Name DOB Age Date of Joining Date of Confirmation Average_Age
1 Laura 11/11/1986 25 12/9/2005 27/11/2005 30
2 Desy 12/5/1981 30 24/6/2000 10/7/2000 30
3 Alex 30/5/1978 33 10/10/1998 24/12/1998 30
4 John 6/6/1979 32 2/12/1999 20/12/1999 30
Supported Functions
Category Function (Function when applied on the Employee table) Description
Date DAYSBETWEEN Returns the number of days between two dates.
  CURRENTDATE Returns the current system date.
  MONTHSBETWEEN Returns the number of months between two dates.

For example, the new column contains 2,0,2,0 when MONTHSBETWEEN([Date of Joining],[Date of Confirmation]) is applied to the Employee table.

  DAYNAME Returns the day name in string format.

For example, the new column contains Monday, Saturday, Saturday, Thursday when DAYNAME([Date of Joining]) is applied to the Employee table.

  DAYNUMBEROFMONTH Returns the day number of the particular month.

For example, 12/11/1980 returns 12.

  DAYNUMBEROFWEEK Returns the day number in a week.

For example, Sunday =1, Monday=2.

  DAYNUMBEROFYEAR Returns the day number in a year.

For example, 1st Jan =1, 1st Feb=32, 3rd Feb=34.

  LASTDATEOFWEEK Returns the date of the last day in a week.

For example, 12/9/2005 returns 17/9/2005

  LASTDATEOFMONTH Returns the date of the last day in a month.

For example, 12/9/2005 returns 30/9/2005

  MONTHNUMBEROFYEAR Returns the month number in a date.

For example, Jan=1, Feb=2, Mar=3

  WEEKNUMBEROFYEAR Returns the week number in a year.

For example, 12/9/2005 returns 38.

  QUARTERNUMBEROFDATE Returns the quarter number in a date.

For example, 12/9/2005 returns 3.

String CONCAT Concatenates two strings.

For example, CONCAT('USA', 'Australia') returns USAAustralia.

  INSTRING Returns true - if the search string is found in the source string.

For example, INSTRING('USA', 'US') returns true.

  SUBSTRING Returns a substring from the source string.

For example, SUBSTRING('USA', 1,2) returns US.

  STRLEN Returns the number of characters in the source string. For example, STRLEN('Australia') returns 9.
Math MAX Returns the maximum value in a column.
  MIN Returns the minimum value in a column.
  COUNT Returns the number of values in a column.
  SUM Returns the sum of the values in a column.
  AVERAGE Returns the average of the values in a column.
Data Manipulation @REPLACE Performs in-place replacement of a string.

For example, @REPLACE([country],'USA', 'AMERICA') replaces USA with AMERICA in the country column.

  @BLANK Replaces blank values with a specified value.

For example, @BLANK([country], 'USA') replaces all blank values with USA in the country column.

  @SELECT Selects rows that satisfy the given condition. You can use any conditional operator to specify the condition.

For example, @SELECT([country]=='USA') selects rows where country is equal to USA.

Conditional Expression IF(condition) THEN(string expression/mathematical expression/conditional expression) ELSE(string expression/mathematical expression/conditional expression) Checks whether the condition is met, and returns one value if 'true' and another value if 'false'.

For example, IF([Date of Joining]>12/9/2005) THEN ('Employee joined after Sept 12, 2005') ELSE ('Employee joined on or before Sept 12, 2005')

Note Mathematical expressions containing functions that return a numerical value are not supported. For example, expression DAYNUMBEROFMONTH(CURRENTDATE())+2 is not supported because DAYNUMBEROFMONTH returns a numerical value.
Mathematical Operators

Use mathematical operators to create formulas containing numerical columns and/or numbers. For example, the expression [Age] + 1 adds a new column with values 26, 31, 34, 33.

Mathematical Operators Description
+ Addition operator
- Subtraction operator
* Multiplication operator
/ Division operator
() Round brackets or parenthesis
^ Power operator
% Modulo operator
E Exponential operator
Conditional Operators
Use conditional operators to create IF THEN ELSE or SELECT expressions.
Conditional Operators Description
== Equal to
!= Not equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
Logical Operators

Use logical operators to compare two conditions and return 'true' or 'false'. For example, IF([Date of Joining]>12/9/2005 && [Age] >=25 ) THEN ('True') ELSE ('False') adds a new column with values True, False, False, False.

Logical Operators Description
&& AND
|| OR