Filter transform

Filter transform

The filter transform allows you to selectively filter data based on filter conditions applied over one or more columns.   

  To apply the filter transform:  

  1. Click the Transform menu and select the Filter option. The Filter pane will slide open to view


  1. Choose any of the following options in the Filter options section:

  • Keep filtered rows - Choosing this option will keep you only the rows that are filtered in your dataset.

  • Delete filtered rows - Choosing this option will delete all the filtered rows and keep the rest in your dataset.

  1. Click the icon to add columns to the filters. The All columns filter is added by default. However, you can add more filters or remove the existing ones. You can also reorder the filters using the drag and drop method.

  1. When you add more than one filter to the Filters section, the logical operators, AND or OR appear next to the filters. You can click to toggle the logical operator between AND and OR.



Using the logical operators, you can combine the conditions and apply logic to determine the rule of precedence. The final expression is displayed in the Criteria expression box. You can click Edit to alter the default expression using logical operators and parenthesis to specify the precedence or the sequential order as to which condition should be evaluated firstClick Save after making the required changes. 

For example, In the expression, ((1 OR 2) AND (3 OR 4)) , at first the condition ( 1 OR 2 ) will be executed and the condition ( 3 OR 4 ) will be executed next. Thirdly, since, the AND operator is used, the filter will be applied when both the conditions are true.


  1. You can use the Clear button to remove all the filters.

  2. For every filter added, you can select one of the following options from the drop-down:

  1. Actual: This option lets you filter rows based on the actual values in the column.
  2. Data quality: This option lets you filter rows based on the quality of data in the column.
  3. Patterns: This option helps you filter rows based on the data patterns in the selected column.
  4. Seasonal: This option helps you filter rows based on the seasonal parameters such as quarter, month, week, etc.
  5. Length: This option helps you filter rows based on the length of the text in the column data.
  6. Outliers: This option allows you to filter rows based on the outliers present in the data of the selected column.          
Note: The filter options are displayed based on the datatype of the column added for the filter.

Filter Options

Filter options are categories using which you can filter values. The filter options are displayed based on the datatype of the column added for the filter.
Let us discuss each filter option in detail.

Actual 

If you had selected the Actual option for the filter, you can further drill down to choose one of the following to select specific values to filter.
      1. Individual values: You can select the required individual values from here. You can also search for values using the Search bar. Click the +Add new data button to add new individual values.

      2. Range: If you choose the Range option, you can select the required range of values. Create your own range using the +Add new range button.

The Range tab is displayed only for number columns.
      1. Wildcard- If you choose the wildcard option, you can construct the required condition using the String or expression field. Click the icon to add more conditions. 

        When you add more than one condition in the
         Wildcard  tab, the logical operators,  AND  or  OR appear next to the conditions. You can click to toggle the logical operator between AND and OR.




 
Using the logical operators, you can combine the conditions and apply logic to determine the rule of precedence. The final expression is displayed in the Criteria expression box. You can click Edit to alter the default expression using logical operators and parenthesis to specify the precedence or the sequential order as to which condition should be evaluated first. Click Save after making the required changes.

For example, In the expression, ((1 OR 2) AND (3 OR 4)), at first the condition (1 OR 2) will be executed and the condition (3 OR 4) will be executed next. Thirdly, since, the AND operator is used, the filter will be applied when both the conditions are true.


Seasonal

If you had selected the Seasonal option for the filter, you can further drill down to choose one of the following options to select the required values to be filtered.   
  • Quarter

  • Month

  • Week

  • Weekday

  • Day of the month

 


 

     

Data quality   

If you had selected the Data quality option for the filter, you can choose to filter rows based on the data quality of the column,             i.e., valid data, invalid data, or missing data in the selected column.

 


 

Patterns   

If you had selected the Patterns option for the filter, the patterns present in the column data will be displayed in the section below. You can select the required pattern  from the list, or create your own custom pattern using the +Add new pattern option.

 


 

 

Outliers   

If you had selected the Outliers option for the filter, the outliers present in the column data will be displayed in the next section below. You can select the required values from the list to filter your data.

      

  1. In the final step, in the selection box, you can choose to include or exclude the values selected in the filter values section using the Include items or Exclude items option .


 

  1. Click the Preview button to view the preview of the filter in the Preview pane.

  2. Click the Apply button to apply the filter transform.




    Zoho DataPrep Personalized Demo

    If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.

    Zoho CRM Training

      Create, share, and deliver

      beautiful slides from anywhere.

      Get Started Now





              Zoho CRM Training Programs

              Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

              Zoho CRM Training


                  Zoho TeamInbox Resources




                            Zoho DataPrep Resources

                              Zoho DataPrep Demo

                              Get a personalized demo or POC

                              REGISTER NOW


                                Design. Discuss. Deliver.

                                Create visually engaging stories with Zoho Show.

                                Get Started Now











                                                      • Related Articles

                                                      • Select transform

                                                        Zoho DataPrep helps you to select, rename, and reorder columns in the dataset. To rename or reorder columns 1. Right-click on a column header and choose the Select columns option from the context menu. 2. To reorder columns, choose the column names ...
                                                      • Pivot

                                                        Pivot Table distributes data for easy consumption. It spreads out the data in long, winding tables by converting categories into columns. A pivot can be created by selecting the Column, Row, and Data fields.  To apply the pivot transform: 1. Click ...
                                                      • Duplicate Column

                                                        Zoho DataPrep allows you to duplicate columns present in your dataset. To duplicate columns 1. Right-click on a column header and select the Duplicate columns option from the context menu. 2. Select the columns from the Columns to duplicate dropdown. ...
                                                      • Smart selection

                                                        Zoho DataPrep offers you an array of suggestions using the pattern matching notations when you select portions of the column data that you wish to transform.  Note: If you are not familiar with pattern matching in DataPrep, read about it here. You ...
                                                      • Keep or delete filtered rows

                                                        You can filter the rows by entering the value in the search box at the top bar of the operations page.  You can also click on the histogram of each column to select the value on which the rows should be filtered. Some of the other options to filter ...

                                                      Resources

                                                      Videos

                                                      Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                      eBooks

                                                      Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                      Webinars

                                                      Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                      CRM Tips

                                                      Make the most of Zoho CRM with these useful tips.



                                                        Zoho Show Resources