Filter

Properties that can be configured for the Filter Preparation Component.

Syntax Use this component to filter rows and columns based on a specified condition.
Note The In-DB Filter component does not support functions and advanced expressions.
Note If you change the data source after configuring the filter component, the filter component still retains the previously defined row filters.
Filter Properties
Table 1: Data Preparation Component Properties
Property Description
Selected Columns Select columns for analysis.
Filter Condition Enter the filter condition.

Example:

Filter "Store" column from the source data and apply "Profit >2000" condition.
Store Revenue Profit
Land Mark 10000 1000
Spencer 20000 4500
Soch 25000 8000
  1. Uncheck the "Store" column from the Selected Columns.
  2. In the Row Filter pane, choose the Profit column.
  3. In the Select from Range option, enter 2000 in the From text box. The To text box should be empty.
  4. Choose OK.
  5. Choose Save and Close.
  6. Execute the analysis.
Output table:
Revenue Profit
20000 4500
25000 8000
Syntax
Note The Filter component only supports expressions that return Boolean result.
For example, in the Employee table below:
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/10/1998
4 John 6/6/1979 32 2/12/1999 20/12/1999
  • The expression DAYSBETWEEN([Date of Joining],[Date of Confirmation]) is not a valid filter expression since it returns a numerical value. The correct usage of the DAYSBETWEEN expression in filter is DAYSBETWEEN([Date of Joining],[Date of Confirmation]) == 14. This expression selects those rows where number of days between "Date of Joining" and "Date of Confirmation" is 14. For the employee table above, the third row is selected.
  • DAYNAME([Date of Joining]) == 'Saturday' selects the second and third rows in the employee table.
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\]].
Supported Functions
Note The Filter component does not support data manipulation 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 the string format.

For example, the new column contains Monday, Saturday, Saturday, Thursday when DAYNAME([Date of Joining]) is applied on 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.

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.
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 the 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