How to use filter transform?

Filter


The filter option allows you to filter data based on custom conditions applied over one or more columns. The filter can also be used without applying them as a rule. However, you can choose to apply them as a rule by either keeping or deleting the filtered rows.

How to search and filter data?

Filter column

You right-click on a column and select the Filter option from the context menu. The Advanced filter pane will slide open to view.



Search and filter box

The search and filter box is exploratory in nature and provides a way to filter your dataset without applying them as a rule. However, you can choose to apply them as a rule by either keeping or deleting the filtered rows.

You can click the  icon and select one of the default filter options using the filter icon from the Search and filter box:
  1. Filter rows with valid values 
  2. Filter rows with invalid values
  3. Filter rows with missing values
  4. Filter rows with missing or invalid values


If you want to filter data based on custom filter conditions, you can use the Advanced option that appears once you filter data. 


Search a specific value

  1. To search data, enter the value in the  Search and filter  box. The searched keyword will be added as a chip below the box based on the default condition '  contains  '.
  2. You can also select the chip and edit the searched keyword and the condition at any time.


  1. Once the search result appears, you can choose to  Keep or Delete the filtered rows. If you want to filter data based on custom filter conditions, you can click the Advanced link. 
All your searched keywords will be automatically included when you open the Advanced filters pane. 

Other means to filter your dataset

To filter dataset by other means, you can simply click on the required portion on the histogram, data quality bar, or the donut chart to filter out those values. 


  1. When you filter, a chip appears below the Search and filter box. You can select the chip and edit the searched keyword and the associated condition at any time. 
  2. You can add multiple filters, and a chip would be added for each filter. To add conditions to the filters, click the Advanced link.
1. All filters present will be automatically included when you open the Advanced filters pane.
2. Including the ones that you add while having the pane open.
3. You can also edit the filters in the Advanced filters pane.

To apply filters  

1. Click on the donut chart, histogram or the default filter options and filter data.

2. Click the Advanced link that appears above the data grid once you filter data. The Advanced filters  pane will slide open to view. 




3. You can also Right-click on a column and select the Filter option from the context menu. The Advanced filter pane will slide open to view.  



     
4. Click the icon if you want to add more columns to the filters. You can also reorder the filters using the drag and drop method.
  1. The All columns filter will apply the filter depending on the values available in all the columns in your dataset.
  2. The Any column filter will apply the filter depending on the value available in at least one of the columns in your dataset.
5. 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.



6. 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.


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

8. 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. 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.



The Individual values tab is displayed for text , decimal and number base datatypes.

2. Range

If you choose the Range option, you can select the required range of values. To customise the range, click the +Add new range button, choose Above , Below or Between , enter the values and click  add.



The Range tab is displayed for number , decimal , date and datetime base datatypes.

3. Wildcard

If you choose the wildcard option, you can construct the required condition using the String or expression field from the available options:
  • Contains

  • Doesn't contain

  • Begins with

  • Doesn't begin with

  • Ends with

  • Doesn't end with

  • Is

  • Is not

  • Matches regex

You can also toggle case-sensitivity and add whitespace, tabspace, newline, and return.
The Wildcard tab is displayed for text base datatype .

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

 



The Seasonal option is displayed only for date and datetime base datatypes.

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.



The Patterns option is displayed for all datatypes except  List and Map datatypes.

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.


      

7. 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.



 

8. Click the Filter button. The number of filters added will be shown in the chip that appears above the data grid.

9.  Advanced filters filter the data without applying any rule. After when you're satisfied with your filtered data, you can choose to apply either the Keep rows or Delete rows rule.



SEE ALSO

    Access your files securely from anywhere

      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
        Redefine the way you work
        with Zoho Workplace

          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 Sign now offers specialized one-on-one training for both administrators and developers.

              BOOK A SESSION





                          Quick LinksWorkflow AutomationData Collection
                          Web FormsEnterpriseBegin Data Collection
                          Interactive FormsWorkplaceData Collection App
                          CRM FormsCustomer ServiceAccessible Forms
                          Digital FormsMarketingForms for Small Business
                          HTML FormsEducationForms for Enterprise
                          Contact FormsE-commerceForms for any business
                          Lead Generation FormsHealthcareForms for Startups
                          Wordpress FormsCustomer onboardingOrder Forms for Small Business
                          No Code FormsConstructionRSVP tool for holidays
                          Free FormsTravel
                          Prefill FormsNon-Profit

                          Intake FormsLegal
                          Mobile App
                          Form DesignerHR
                          Mobile Forms
                          Card FormsFoodOffline Forms
                          Assign FormsPhotography
                          Mobile Forms Features
                          Translate FormsReal EstateKiosk in Mobile Forms
                          Electronic Forms

                          Notification Emails for FormsAlternativesSecurity & Compliance
                          Holiday FormsGoogle Forms alternative GDPR
                          Form to PDFJotform alternativeHIPAA Forms
                          Email Forms
                          Encrypted Forms
                          Embeddable Forms
                          Secure Forms
                          Drag & drop form builder
                          WCAG



                                            You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.




                                                Manage your brands on social media


                                                  • Desk Community Learning Series


                                                  • Digest


                                                  • Functions


                                                  • Meetups


                                                  • Kbase


                                                  • Resources


                                                  • Glossary


                                                  • Desk Marketplace


                                                  • MVP Corner


                                                  • Word of the Day


                                                  • Ask the Experts


                                                    Zoho Marketing Automation

                                                      Zoho Sheet Resources

                                                       

                                                          Zoho Forms Resources


                                                            Secure your business
                                                            communication with Zoho Mail


                                                            Mail on the move with
                                                            Zoho Mail mobile application

                                                              Stay on top of your schedule
                                                              at all times


                                                              Carry your calendar with you
                                                              Anytime, anywhere




                                                                    Zoho Sign Resources

                                                                      Sign, Paperless!

                                                                      Sign and send business documents on the go!

                                                                      Get Started Now




                                                                              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

                                                                                                              • Dataset transforms

                                                                                                                Dataset transform consists of combining and transforming datasets to suit your needs. You can apply the following transforms in a dataset. Deduplicate Row-wise Column-wise Join Append Pivot Unpivot Calculated columns Derive dataset Mark as personal ...
                                                                                                              • Derive dataset

                                                                                                                The Derive dataset transform allows you to create a branch of your dataset from the last applied transform in your dataset. The new dataset will have the most recent state of your data with an empty ruleset. To derive dataset 1. In the DataPrep ...
                                                                                                              • Window functions to add formula columns

                                                                                                                A window function is used to perform calculations across a group of rows on a dataset. A set of rows is termed a window. You can use window functions to perform summations and calculations based on a rolling window of data, relative to the current ...
                                                                                                              • Add formula

                                                                                                                You can create new columns and customize them using the Formula column transform. Zoho DataPrep offers a variety of functions to suit your needs. Click here to know more about the functions. To create a formula column 1. Click on the Transform menu ...
                                                                                                              • Target Matching

                                                                                                                Target matching happens before the data is exported to the destination. Target matching is a useful feature in DataPrep that prevents export failures caused due to errors from the data model mismatch. Using target matching, you can set the required ...
                                                                                                                Wherever you are is as good as
                                                                                                                your workplace

                                                                                                                  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