Complex Data Filters

In the 'Filter Data' step of the import wizard, you have sophisticated control over which records are imported.

To define complex filter during import:

  1. Select FILE | Import and then the data source
  2. In the Define Filter step of the import wizard, select the 'Complex Filter' checkbox and then click on 'Define Filter'

To change complex filter after import:

  1. Select REFRESH | Refresh Properties | Define Filter
  2. Select the 'Complex Filter' checkbox and then click on 'Define Filter'

Comparison Operators

Operator Description Example
== equal to [Department] == "Strategy"
!= not equal [Department] != "Strategy"
> not equal [Salary] != 100000
< less than [Salary] < 100000
>= greater than or equal to [Salary] >= 100000
<= less than or equal to [Salary] <= 100000

Logical Operators

Operator Description Example
&& and [Department] != "Strategy" && [Department] != "Planning"
|| or [Department] == "Strategy" || [Department] == "Planning"
! not !(StartsWith([CostCenter],"999")

String Functions

Operator Description Example
ToUpper() Convert string to upper case ToUpper([Department])
ToLower() Convert string to lower case ToLower([Department])
Contains() Test if a string contains a string Contains([Department], "001"))
StartsWith() Test if a string starts with a string StartsWith([Department],"001"))
EndsWith() Test if a string ends with a string EndsWith([Department],"001"))

Date Functions

Operator Description Example
ToDate() Convert string to date ToDate("12/1/2017")
TODAY Today's date ToDate([TODAY])
TODAYF Today's date including current time ToDate([TODAYF])
TODAYF+N Today's date + N days ToDate([TODAYF+5])
TODAYF-N Today's date - N days ToDate([TODAYF-5])

NOTE: TODAY should be used to test if a date is equal to today. Example: ToDate([HireDate]) == ToDate([TODAY]). TODAYF should be used for all other comparisons.

Some Examples

Example Notes
[Department] != "Strategy" && ([Department] != "Planning" || [CostCenter] == "999") [Department] != "Strategy" AND ([Department] != "Planning" OR [CostCenter] == "999")
ToDate([HireDate]) <= ToDate([TODAYF]) True if "HireDate" is not in the future
EndsWith(ToUpper([CostCenter]),"SE9") True if cost center ends with "SE9" or "se9" or "Se9" or "sE9"
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License