You can create the following type of data filters for any type of task:
•Simple
•Advanced
You can create a set of data filters for each object included in a replication task or synchronization task. Each set of data filters acts independently of the other sets.
Simple data filters
You can 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
>=
100000
NumberOfEmployees
>
500
Configuring simple data filters
You configure simple data filters in the task wizard.
1 To create a simple data filter, perform one of the following actions:
- On the Data Filters page, click Simple, and then click New.
The Data Filter dialog box appears.
- In the Filters area on the Sources page, select Simple and then click New.
- In a data transfer task, in the Filters area on the Source or Second Source page, select Simple and then click New.
2Specify the object on which to create the data filter.
You create separate data filters for each source object included in the task.
3Enter the filter condition based on the field, operator, and field value.
4Click OK.
5Create additional simple data filters as needed.
To delete a data filter, click the Delete icon next to the data filter.
6Click Next.
Advanced data filters
Create an advanced data filter to create complex expressions that use AND, OR, or nested conditions.
When you create an advanced data filter, 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 mappings in SQL ELT mode, use expression syntax that is valid in your cloud data warehouse. For more information, see the documentation for your cloud data warehouse. For other types of mappings, use Informatica transformation language expression syntax.
For example, you create a mapping to 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.
Configuring advanced data filters
Configure advanced data filters in the task wizard. You can convert simple data filters to an advanced data filter, but you cannot convert an advanced data filter to simple data filters.
1To create an advanced data filter, perform one of the following actions:
- 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.
- In the Filters area on the Sources page, select Simple and then click New.
- In a data transfer task, in the Filters area on the Source or Second Source page, select Advanced.
To convert all simple data filters to one advanced data filter, select Advanced.
2If necessary, specify the object on which to create the data filter.
You create separate data filters for each source object included in the task.
3Enter the filter expression.
Click the field name to add the field to the expression.
4Click OK.
To delete a data filter, click the Delete icon next to the data filter.
5Click Next.
Data filter operators
You can use specific operators with each field type.
For mappings in SQL ELT mode, use operators that are valid in your cloud data warehouse's expression language. For more information, see the documentation for your cloud data warehouse. For other types of mappings, use the Informatica transformation language operators that are listed in the following table.
The following table shows the operators you can use for each field type:
Field type
Operators
Boolean
=, !=, Is Null, Is Not Null
Currency
=, !=, <, <=, >, >=, Is Null, Is Not Null
Date
=, !=, <, <=, >, >=, Is Null, Is Not Null
Datetime
=, !=, <, <=, >, >=, Is Null, Is Not Null
Double
=, !=, <, <=, >, >=, Is Null, Is Not Null
ID
=, !=, Is Null, Is Not Null
Int
=, !=, <, <=, >, >=, Is Null, Is Not Null
Reference
=, !=, Is Null, Is Not Null
String
=, !=, LIKE'_%', LIKE'%_', LIKE'%_%', Is Null, Is Not Null, <, <=, >, >=
Textarea
=, !=, LIKE'_%', LIKE'%_', LIKE'%_%', Is Null, Is Not Null, <, <=, >, >=
All other field types
=, !=, 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 data filter variables:
Variable
Description
$LastRunDate
The start date in GMT time zone of the last task run that was successful or ended with a warning. Does not include time. For example, 2018-09-24. Can be used as a value for filter where the field type is DATE.
$LastRunTime
The start date and time in GMT time zone of the last task run that was successful or ended with a warning. For example, 2018-09-24 15:23:23. Can be used as a value for filter where the field type is DATETIME.
You can't use the $LastRunTime variable with DATE fields .
You can't use data filter variables in expressions in mappings SQL ELT mode.
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 Informatica Intelligent Cloud Services. 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:
•When you create a data filter in a mapping in SQL ELT mode, use expression syntax that is valid in your cloud data warehouse. For more information, see the documentation for your cloud data warehouse. For other types of mappings, use Informatica transformation language expression syntax.
•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 data types of the fields change. If a data type changes, edit the task.
•You can select Equals, Not Equals, Is Null, or Is Not Null operators on fields of the Other data type.
•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 data types.
•If you specify a date and no time for a date/time filter, Data Integration uses 00:00:00 (12:00:00 a.m.) as the time.
•You cannot create a simple data filter in a 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 data type 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.
•When you use a parameter in a data filter, start the data filter with the parameter. For example, use $$Sales=100000 instead of 100000=$$Sales.