Deduplicate
You can remove duplicate records from your data using the Deduplicate transform. This can be done in two ways: row-wise and column-wise.
Row-wise
This method removes rows with duplicate data, allowing only unique rows to be present in your dataset.
To apply row-wise deduplication:
1. Click the Transform menu, click Deduplicate, then select Row-wise.
2. When removing duplicate rows, you can enable these options to refine how duplicates are identified:
Ignore case - This option ignores the cases and treats uppercase and lowercase characters as the same and consider them as duplicates.
Ignore whitespace - This options ignores leading, trailing, and multiple spaces between words and consider them duplicates
Flag duplicate records - This option will flag duplicate records in a new column. You can filter out duplicates using the newly added column later. For records that have no duplicates, the 'Duplicate Flag' column will remain empty. This option helps you preserve both the master and duplicate records, while still identifying the duplicate records. Instead of immediately removing duplicates, you can review, validate, or apply transforms before deciding what to keep or discard.
Here's a snapshot below,
Note: There could be instances where no duplicates were found in the sample dataset. You can still apply the rule to remove duplicates rows when the entire dataset is processed during export.
3. A live preview will be shown with the duplicate rows highlighted in red.
4. Click Remove duplicates.
Column-wise
You can also select single or multiple columns and choose to deduplicate. You can use Deduplicate > Column-wise transform to remove rows based on duplicate values present in the selected columns.
In other words, select all those columns (For example, Region, Address, Product) which has the same entries vertically. The column-wise deduplication will remove those rows which has the same entry vertically in the columns selected.
To apply column-wise deduplication:
1. Click the Transform menu, click Deduplicate then select Column-wise.
2. When removing duplicate rows, you can enable these options to refine how duplicates are identified:
Ignore case - This option ignores the cases and treats uppercase and lowercase characters as the same and consider them as duplicates.
Ignore whitespace - This options ignores leading, trailing, and multiple spaces between words and consider them duplicates
Flag duplicate records - This option will flag duplicate records in a new column. You can filter out duplicates using the newly added column later. For records that have no duplicates, the 'Duplicate Flag' column will remain empty. This option helps you preserves both the master and duplicate records, while still identifying the duplicate records. Instead of immediately removing duplicates, you can review, validate, or apply transforms before deciding what to keep or discard.
Here's a snapshot below,
3. You can choose one of the two methods to dedupe your dataset based on the selected column: Automatic deduplication or Manual conditions.
4. When you choose the Automatic deduplication method, DataPrep works for you to deduplicate your data based on the columns you've selected.
5. When you choose the Manual conditions method, you will need to enter the conditions and expressions and construct the 'if' statements. You can then select which rows to keep, or remove, within each of the duplicate cluster if the condition is true.
6. The following table lists the available If conditions for all the data types. Click here to know more about data types.
|
Text |
Numeric |
Datetime |
Duration |
Boolean |
List |
Map |
|
contains |
=equal to |
= equal to |
is smallest |
is true |
has value |
has key |
|
doesn't contain |
!= not equal to |
!= not equal to |
is largest |
is false |
is empty list |
is empty map |
|
begins with |
> more than |
is earliest |
= equal to |
contains |
is not empty list |
is not empty map |
|
ends with |
< less than |
is latest |
!= not equal to |
doesn't contain |
is empty |
is empty |
|
is |
>= more than or equal |
is after |
is empty |
begins with |
is not empty |
is not empty |
|
is not |
<= less than or equal to |
is before |
is not empty |
ends with |
use regex |
use regex |
|
is empty |
is smallest |
on or after |
use regex |
is |
use patterns |
use patterns |
|
is not empty |
is largest |
on or before |
use patterns |
is not |
|
|
|
use regex |
is empty |
is empty |
|
is empty |
|
|
|
use patterns |
is not empty |
is not empty |
|
is not empty |
|
|
|
|
use regex |
use regex |
|
use regex |
|
|
|
|
use patterns |
use patterns |
|
use patterns |
|
|
7. You can also keep adding more conditions using the AND and OR operators to apply deduplication using a combination of conditions.
For example, you can write a condition that goes like this, "If the mail column contains zoho.com, keep those rows", i.e., Enter conditions to select which rows to keep if mail contains zoho.com
8. With the Advanced option, you can insert functions and provide conditions to remove duplicates.
9. Click the Preview button to see which rows will be removed during the transformation.
10. You can also select multiple columns for deduplication using (+) in Columns to de-duplicate.
FAQs
1. Why do the number of duplicates reduce each time I apply the deduplicate by rows transform with the Flag duplicates option?
Each time you apply the deduplicate transform with the flag option, a new column is added to mark duplicate records. When you apply it again, this flag column is also checked for duplicates. Because all flagged records now have the same flag value, they are grouped together. So the number of duplicates keeps reducing each time you apply it.
Example:
First time → 5 records → 4 duplicates, 1 master
Second time → 3 duplicates, 1 master
Third time → 2 duplicates, 1 master
To avoid this, make sure you filter out and delete the duplicate records before applying the transform again.
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 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 Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
New to Zoho Writer?
Create. Review. Publish.
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
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.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
How to export modified data to the cloud database?
This guide will help you sync data from a source application or database to a cloud data warehouse, transferring only the new and modified records from the source system to the destination data warehouse. Let's assume you want to sync data from an ...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 ...What is Zoho DataPrep?
What is Zoho DataPrep? Zoho DataPrep is an advanced self-service data preparation software. It offers a comprehensive and automated solution to model, cleanse, prepare, enrich and organize large volumes of data from multiple data sources to serve ...Data migration for Zoho Creator using DataPrep
Zoho DataPrep is now integrated with Creator using Zoho Creator connector. The forms in Zoho Creator are used to collect and store data, serving as the main point of interaction between the created application and users. Zoho DataPrep allows you to ...Export data to Zoho Directory [Private Preview]
Zoho DataPrep supports exporting data to Zoho Directory. Zoho Directory is an identity and access management software that allows you to authenticate and manage access provision to multiple products. Note: This integration is in Private Preview, ...
New to Zoho LandingPage?
Zoho LandingPage Resources