Although a separate Filter transformation can be added to a mapped domain, filters can also be applied directly to a source table or a variable transformation.

To limit the data retrieved in a particular transformation, use the Filter Expression property. 

Add a Filter to a Transformation

A separate Filter transformation can also be added to the domain from the Variable Mapping options.

1. In the Domain Properties subpanel, click the ellipsis (...) next to the Filter Expression field. 
   
   The Filter window opens.
   
2. In Filter Columns, select the field to filter. Scroll through the list or type into the field to narrow results.
    
3. In Filter Functions, select the function to use. Available functions depend on the data type of the selected column and may include:
   • Actual Value: Filter on the actual value of the field.
   • Lower: Convert a text field to lowercase for the comparison.
   • Upper: Convert a text field to uppercase for the comparison.
   • Day: For date fields, only evaluate the day.
   • Month: For date fields, only evaluate the month.
   • Year: For date fields, only evaluate the year.
4. In the Operator box, select an operator for the filter. Operators vary based on data type and may include:
   • Between
   • Contains
   • Ends With
   • Equals
   • In List (click the In List link for more information)
   • Is Greater Than
   • Is Greater Than or Equal
   • Is Less Than
   • Is Less Than or Equal
   • Is Null
   • Is Null or Empty

   • Starts With

     Note: Select the Not checkbox to apply the logical NOT of the selected operator.

5. In the Value field, click the drop-down to select a value for the filter, or enter a value manually.
   • To compare two columns, select Column and choose a column from the list.
   • Example: To display all records where the start date equals the end date, compare the Start Date field to the End Date column.
6. Click Add AND Condition to add the filter. The selection displays in the window below.
7. Continue adding filters as needed, selecting AND or OR as needed.
   • Example: Combine multiple filters such as (a OR b) AND c.
   • To group filters, select an existing condition before clicking Add AND Condition or Add OR Condition.

8. Click Save to return to the Domain Editor window. The results in the preview/SQL area reflect the filter, and the Filter Expression field displays Filter Set.

Use the In List Operator

When using the In List operator:

1. Click Edit to open the Choose Values window.
   
   
    
2. Select the items from the list and click Add >. 
   • Multiple selections can be made using Ctrl+Click or Shift+Click.
3. In the Other Values field, enter additional values not in the list and click Add (>).
4. Click Save to return to the Filter window.

Set the Sort Order

The sort order can be managed at any level of a mapping. It is most effective when applied to the outermost layer, although nesting rules may influence the final order.

• To set the sort order, enter the field name in the Sort field in the Domain Properties subpanel.
• To sort on multiple fields, enter field names separated by commas.
• To sort a field in descending order, enter the field name followed by desc.
  • Example: To sort by the Subject field in descending order, enter SUBJECT desc.

Use Unique Values

To limit the results of a transformation to unique values and remove duplicates, select the Unique Values checkbox.