Data Filters
You can create the following type of data filters for any type of task:
You can create a set of data filters for each object included in a Data Replication task or Data Synchronization task. Each set of data filters act independently of the other sets.
Simple Data Filters
Create one or more simple data filters. When you create multiple simple data filters, the associated task creates an AND operator between the filters and loads rows that apply to all simple data filters. For example, you load rows from the Account Salesforce object to a database table. However, you want to load only accounts that have greater than or equal to $100,000 in annual revenue and that have more than 500 employees. You configure the following simple data filters:
Field | Operator | Field Value |
|---|
AnnualRevenue | greater than or equals | 100000 |
NumberOfEmployees | greater than | 500 |
To configure a simple data filter, perform the following steps in the task wizard:
1. On the Data Filters page, click Simple, and then click New to create a data filter.
The Data Filter dialog box appears.
2. Specify the object on which to create the data filter.
You create separate data filters for each source object included in the task.
3. Enter the filter condition based on the field, operator, and field value.
4. Click OK.
5. Create additional simple data filters as needed.
6. To delete a data filter, click the Delete icon next to the data filter.
7. Click Next.
Advanced Data Filters
Create an advanced data filter to create complex expressions that use AND, OR, or nested conditions. You enter one expression that contains all filters. The expression that you enter becomes the WHERE clause in the query used to retrieve records from the source.
For example, you load rows from the Account Salesforce object to a database table. However, you want to load records where the billing state is California or New York and the annual revenue is greater than or equal to $100,000. You configure the following advanced filter expression:
(BillingState = 'CA' OR BillingState = 'NY') AND (AnnualRevenue >= 100000)
When you create a data filter on a Salesforce object, the corresponding task generates a SOQL query with a WHERE clause. The WHERE clause represents the data filter. The SOQL query must be less than 20,000 characters. If the query exceeds the character limit, the following error appears:
Salesforce SOQL limit of 5000 characters has been exceeded for the object: <Salesforce object>. Please exclude more fields or decrease the filters.
Note: Filter conditions are not validated until runtime.
To configure an advanced data filter, perform the following steps in the task wizard:
1. To create an advanced data filter, on the Data Filters page, click New > Advanced.
To convert all simple data filters to one advanced data filter, on the Data Filters page, select a simple data filter and then click Advanced. You cannot convert an advanced data filter back to simple data filters.
2. When you configure a data filter, specify the object on which to create the data filter.
You create separate data filters for each source object included in the task.
3. Enter the filter expression.
Click the field name to add the field to the expression.
4. Click OK.
5. To delete a data filter, click the Delete icon next to the data filter.
6. Click Next.
Data Filter Operators
You can use the specified operators for each of the following general field types:
Field Type | Supported Operators |
|---|
Boolean | Equals, Not Equals, Is Null, Is Not Null |
Currency | Equals, Not Equals, Less Than, Less Than or Equals, Greater Than, Greater Than or Equals, Is Null, Is Not Null |
Date | Equals, Not Equals, Less Than, Less Than or Equals, Greater Than, Greater Than or Equals, Is Null, Is Not Null |
Datetime | Equals, Not Equals, Less Than, Less Than or Equals, Greater Than, Greater Than or Equals, Is Null, Is Not Null |
Double | Equals, Not Equals, Less Than, Less Than or Equals, Greater Than, Greater Than or Equals, Is Null, Is Not Null |
ID | Equals, Not Equals, Is Null, Is Not Null |
Int | Equals, Not Equals, Less Than, Less Than or Equals, Greater Than, Greater Than or Equals, Is Null, Is Not Null |
Reference | Equals, Not Equals, Is Null, Is Not Null |
String | Equals, Not Equals, Starts With, Ends With, Contains, Is Null, Is Not Null, Less Than, Less Than or Equals, Greater Than, Greater Than or Equals |
Textarea | Equals, Not Equals, Starts With, Ends With, Contains, Is Null, Is Not Null, Less Than, Less Than or Equals, Greater Than, Greater Than or Equals |
All other field types | Equals, Not Equals, Is Null, Is Not Null |
Data Filter Variables
Data filter variables represent the date or time that a task previously ran. Use data filter variables to help capture the source data that changed since the last task run. You can use data filter variables in simple and advanced data filter conditions.
You can use the following table data filter variables:
Variable | Description |
|---|
$LastRunDate | The last date in GMT time zone when the task ran successfully. Does not include time. For example, 2008-09-24. Can be used as a value for filter where the field type is 'DATE.' |
$LastRunTime | The last date and time in GMT time zone when the task ran successfully. For example, 2008-09-24 15:23:23. Can be used as a value for filter where the field type is 'DATETIME.' You cannot use the $LastRunTime variable with DATE fields. |
For example, you can include the following simple filter condition:
LastModifiedDate > $LastRunTime
Note: Consider time zone differences when comparing dates across time zones. The date and time of the $LastRunDate and $LastRunTime variables are based on the time zone set in the Informatica Cloud application. The date and time of the actual job is based on the GMT time zone for Salesforce sources and the database server for database sources. The difference in the time zones may yield unexpected results.
Rules and Guidelines for Data Filters
Use the following rules and guidelines for data filters:
- •Data filters must contain valid SQL or SOQL operators.
- •You cannot include simple and advanced data filters in the same task.
- •When you convert a simple data filter to an advanced data filter, you cannot convert the advanced data filter back to a simple data filter.
- •A task fails if the fields included in the data filter no longer exist or if the datatypes of the fields change. If a datatype changes, edit the task.
- •You can select Equals, Not Equals, Is Null, or Is Not Null operators on fields of the Other datatype.
- •Applications do not apply filters with Equals, Starts With, or Ends With operators and string fields that contain data that starts or ends with a single quotation mark. To filter these records, use the Contains operator.
- •You can only use IS NULL and LIKE operators in data filters for fields of the Text, Ntext, and Image datatypes.
- •If you specify a date and no time for a date/time filter, Informatica Cloud uses 00:00:00 (12:00:00 a.m.) as the time.
- •You cannot create a simple data filter in a Data Synchronization task that includes a flat file source. You can create an advanced data filter.
- •The list of available operators in a simple data filter depends on the datatype of the field included in the data filter. Some operators do not apply to all fields included in data filters.
- •When you enter more than one simple data filter, applications filter rows that meet the requirements of all data filters.