Creating Filters

Through the Analyze tab in Monarch Data Prep Studio, you can create a summary and then apply a filter to that summary so that only the records you are interested in are shown.

You can create the following three filter types in Monarch Data Prep Studio:

• Formula-based filters

• Value-based filters

• Compound filters

The steps to create these filters are detailed in this section.

Creating formula-based filters

To create a formula-based filter, you create a filter expression. A filter expression is a set of rules that tells Monarch Data Prep Studio which records to display. You can think of a filter expression as a continuation of the instruction:

"Display only those records where..."

For example, the expression Customer="fandangos records" tells Monarch Data Prep Studio to:

"Display only those records where the Customer field value is equal to 'fandangos records'."

Steps:

1. In the Analyze tab, click the Add Filter button and then click the Formula button from the options that display.

The Edit Formula Filter window displays.

 

2. Enter an appropriate name for the filter in the Filter Name box.

3. Enter a filter expression in the Expression box at the bottom of the Formula tab.

   Note:

   • You can choose fields, operators, and functions to add to your expression from the lists or you can type the expression manually.

5. Click OK to accept the filter definition; otherwise, choose Cancel.

ADVANCED TAB

In the Advanced tab in the Edit Formula Filter window, you can modify how Monarch Data Prep Studio handles duplicates in your summary.

 

The following settings are available in the Advanced tab:

Field	Description
Don’t care about duplicates	Select if you want duplicates to be ignored (i.e., duplicate fields will not be displayed).
Select rows according to their uniqueness with respect to the specified keys	Select if you want Monarch Data Prep Studio to select only rows that satisfy the filter expression AND have unique values with respect to the selected keys. When selecting this option, select one or both of the following: Unique rows:  Select to have Monarch Data Prep Studio show only unique rows (i.e., no duplicate rows) for the specified keys. Duplicated rows: Select to have Monarch Data Prep Studio show only rows that satisfy the filter expression AND are such that there exist only rows which have duplicate key values. One row only: Select to have Monarch Data Prep Studio display only one of the duplicate rows for any of the specified key(s). All duplicated rows: Select to have Monarch Data Prep Studio display all of the duplicated rows for the specified key(s).
Specified keys	Click on the adjacent check boxes to specify the fields (keys) to which you want to apply the selected duplicate handling option. For this option to be available, you must select the Select rows according to their uniqueness with respect to the specified keys option above.
Row Count Limit	These options are provided to allow you to place an absolute limit on the number of records available to the summary window. Note: The row count limit applies after rows have been selected via the filter expression and the duplicate handling option.
No limit	Use this setting to return all records that pass the filter expression.
First n Rows	Use this setting to limit the number of records returned by the filter.

 

Creating value-based filters

A value-based filter allows you to specify a value (or values) for a particular field. Any field that contains a value that matches those specified in the filter will then be selected and displayed while fields that contain values that don't match those in the filter will not be selected (i.e., they will not be displayed in the summary).

Once defined, a value-based filter may be used as part of a compound filter.

Steps:

1. In the Analyze tab, click the Add Filter button and then click the Value button from the options that display.

The Edit Value Filter window displays.

 

2. Enter an appropriate name for the filter in the Filter Name box.

3. From the Field drop-down list, select the field for which you'd like to specify values to be selected by the filter.

4. Add values to the Values list. There are many ways to interact with the Values grid to add, edit, and delete values.

   • Click on Import values filtered by... button to display a list of filters and the Import button.

   • 

      

     You can then select No Filter from the list of filters and then click Import to automatically populate the value list with all the values of the field.

   • Select a row then click on the Remove selected rows button to delete a row.

   • Notes

     • Wildcard values

     • If the data type of the selected field is a Text, the values specified for matching may include wildcard characters “*” and/or “?”. For example, if one specified a value of “M*” for the City field, this will match all City values which start with the letter “M”, such as “Marietta” and “Montevideo.”

     • Invalid values

     • When the selected field has a data type of Numeric or Date/Time and you enter a Character value, the value list will show invalid values indicated by :

A value is invalid if it cannot be converted into the data type of the selected field. For instance, the value "M*” would be invalid if the selected field type was Date/Time. With invalid values in the value list, you won't be able to proceed with filtering your summary.

 

1. • Right-click on any row to paste copied data. Click Paste Data from Clipboard... to display the following options:

1. 1. • Replace: Choose this option to replace  the data on the selected row with values from the clipboard.

1. 1. • Insert: Choose this option to insert data from the clipboard after the currently selected cell.

      • Append: Choose this option to append data form the clipboard to the end of the table.

2. Set Row Selection options.

   The default option for row selection by a value-based filter is Include rows where the field values matches one of the selected values. This means that the value list specifies the values for the rows which are to be included.

   The alternative option is Include rows where the field value does not match any of the selected values. This means that the value list specifies the values for which rows are to be excluded.

   The Include rows where the field value is null or empty option presents a choice on how the filter is to deal with null (for any field type) and empty values (for Text types only). This choice is completely independent of the specified list of values (it is impossible to specify an empty string as a value). When this option is not checked, rows for which the field value is null (or empty) do not pass the filter. When this option is checked, such rows do pass the filter.

3. Click the Advanced tab to set how many rows will be displayed after the filter is applied.

4. When you have finished configuring the filter, click OK. Otherwise, click Cancel.

Notes

• Values may be directly edited in the cells in the Value column. After adding a value, click anywhere outside the cell to add the value.

• One or more values that were previously copied to the clipboard may be directly pasted into the grid. Rows are automatically added to the grid to accommodate these new values.

• One or more rows may be highlighted by clicking and/or dragging in the extreme left-most column. All the highlighted rows may then be deleted by clicking the Remove Selected Rows button or pressing the Delete on your keyboard.

 

Creating compound filters

A compound filter is simply a combination of one or more filters.

Note:

• Before you can create a compound filter, you must create at least one formula-based filter. After creating a formula-based filter, you can create a compound one. Although you would expect to need two formula-based filters to create a compound filter, you can create a compound filter that is a duplicate of the one existing filter with a row limit applied.

Steps:

1. In the Analyze tab, click the Add Filter button and then click the Compound button from the options that display.

The Edit Compound Filter window displays.

 

2. Enter an appropriate name for the filter in the Filter Name box.

3. Add the desired filters to the Component Filters box by selecting them from the Available Filters box and clicking the Add button.

   In order to define the logic of the filters, select one of the Select rows that satisfy options. Selecting all of the component filters selects the AND logic, whereas selecting the any one of the component filters option selects the OR logic. For example, consider the following compound filter expression:

   [Spinning Records] AND [Medial Sales of 10 or More] AND [Symphonies]

   This would select all transactions of Spinning Records with 10 or more media sales that are described as symphonies as a result of using the "all of the component filters" option.

   [Spinning Records] OR [Medial Sales of 10 or More] OR [Symphonies]

   This would select any transactions that involved Spinning Records, 10 or more media sales, or transactions described as symphonies as a result of using the "any one of the component filters" option. With this setting, we would get all listings involving the customer Spinning Records, all transactions with more than 10 media sales, and all listings described as symphonies.

   Note: In the Component filters box, you can click on any filter and mark the Negate checkbox to make it an inverse filter (i.e., using the NOT logic). The compound filter expression box will then show the updated expression.

4. Click the Advanced tab if you wish to set a row count limit.

   By default, there is no row count limit (i.e. all rows are returned). If you wish to set a limit, define a value for First n rows.

5. Click OK to accept the filter definition. Otherwise, click Cancel.