Toad Data Point 5.3 - User Guide

Filter Data in Transform and Cleanse

To filter data in the Transform and Cleanse tool, create a Filter Data rule. A filter data rule can be simple or complex.

• A simple filter rule can have several conditions, but uses only one type of operation. To create a simple filter rule, follow the instructions in the first procedure in this topic. The following are examples of simple filter rules:

  A and B

  A or B

  not A and not B

  A and B and C

• A complex filter rule is one in which parts of the expression are grouped by parentheses to establish which conditions to evaluate first . To create a complex filter rule, use the second procedure in this topic. The following are examples of complex filter rules:

  (A and B) or C

  A and (B or C)

  (A and B) or (C and D)

To create a simple Filter Data rule

1. Send data to the transform and cleanse window. See Transform and Cleanse Data for more information.

2. Right-click in the column you want to use for filtering data and select Filter Data. The rule editor opens.

   Tip: To create a quick filter, select a column to filter by and then click in the row below the column header. Enter a value or partial text string.

3. In the rule editor, build the first condition.
   1. Use the selected column or select a different column.
   2. Select an operator.
   3. Select or enter a value. To use another column's value here, click and select the other column. This allows you to compare the first field to another field in the row.
   4. If using Is any of, click to the right of the condition to add another value to a list of values.
4. Click Preview at any time to preview the filtered data.
5. If you want to delete your work and clear the rule editor, click Clear. To remove only one condition, click the delete button to the right of the condition.
6. To add another condition to the filter expression, click .

7. The Boolean operation used to join the two conditions is located above the two statements. And is used by default. To use a different Boolean operation, click And and select a different operator (Or, Not And, Not Or).

   Graphic Display	Translates to ...
   	A And B
   	A Or B
   	Not A And Not B

8. To add a third condition using the same Boolean operation, click .
9. To add a second Boolean operator, you must group conditions (add parentheses) . See the following procedure to learn how to translate your complex expression into a diagram in the filter editor.
10. When your filter is finished, click Apply Rule in the filter editor.

To create a complex Filter Data rule

1. Plan your filter. To create a complex filter rule, plan out your filter before building it in the rule editor. Identify grouped conditions (those in parentheses) and nested groups. Identify the Boolean operator for the main expression. Add parentheses to your expression if necessary to help clarify which conditions should be evaluated first.

   Note: Grouped conditions (those in parentheses) must be added to the diagram as a group. You cannot group them later.

2. The filter editor diagram. The filter editor displays your filter expression as a hierarchical diagram. The Boolean operator for the main expression is at the top. Each condition or group in the main expression is displayed below that. If a group contains a sub-group, it is displayed below its parent group.

   Before building your expression, consider the following:

   • Grouped conditions (those in parentheses) must be added to the diagram as a group.
   • The main expression can be made up of both grouped and single conditions.
   • A group has the same structure visually as the main expression—the Boolean operator is located above the conditions it joins.
   • A group must have two or more conditions in it.
   • A group can have only one operator.
   • A sub-group is evaluated before its parent group. Groups are evaluated before the main expression.
3. Add an operator. To build your expression, select a Boolean operator to use for the main expression. And is used by default. To use a different Boolean operation, click And and select a different operator.
4. Add a single condition. If the first part of your complex expression is a single condition, build the condition using the previous procedure for creating a simple filter rule.
5. Build a group. If the first part of your complex expression is a set of grouped conditions (in parentheses), add a group.
   1. To add a group, click and select Add Group.
   2. At the top of the group, select a Boolean operator to use to join the conditions in the group.
   3. Build the first condition in the group.
   4. Click to add a second condition to the group. Repeat this step until all the conditions in the group are added.
   5. To add a sub-group to the group, click after the group's Boolean operator and select Add Group.

6. Add the second part of your complex expression. Continue adding groups and/or single conditions until your complex filter is finished. Review the following examples:

   Graphic Display	Translates to ...
   	(A and B) or C
   	A and (B or C)
   	(A and B) or (C and D)

7. Click Apply Rule in the filter editor to save the Filter rule.

Notes:

• If necessary, Toad simplifies the graphic display of your expression after you save the rule. For example, if you add a group containing only one condition, Toad moves that condition into the parent group.
• If you add a group, the Boolean operator for the group is applied only if there are two or more conditions.

Related Topics

Transform and Cleanse Data