Examples shown on this page may be found here.
This following data set will be used to show how each feature changes the reports output.
Data set
Duplicates
ReportsDNA provides several options for handling duplicate records. These options can be used to find, eliminate or aggregate duplicate records.
Examples shown on this page may be found here.
This following data set will be used to show how each feature changes the reports output.
Data set
| ID | First Name | Last Name | Order |
| 1 | Jane | Doe | Milk |
| 2 | John | Doe | Eggs |
| 3 | Eleni | Smith | Milk |
| 1 | Jane | Doe | Butter |
This setting determines how duplicates will be handled. The available settings and their results are detailed below.
Returns all records n/a Only returns records that are duplicates ID same as Remove Duplicates ID
Setting
Description
Unique ID
Result
All Records
ID
First Name
Last Name
Order
1
Jane
Doe
Milk
2
John
Doe
Eggs
3
Eleni
Smith
Milk
1
Jane
Doe
Butter
Remove Duplicates
Removes all duplicates from the data set.
This will result in a set of all unique records.
ID
ID
First Name
Last Name
Order
1
Jane
Doe
Milk
2
John
Doe
Eggs
3
Eleni
Smith
Milk
Find Duplicates
(i.e., records that would be removed if the
Remove Duplicates function were set)
ID
First Name
Last Name
Order
1
Jane
Doe
Butter
Find Unique
same as Remove Duplicates
Highlight Unique
Highlights all records that are unique
as defined by the Unique ID
ID
ID
First Name
Last Name
Order
1
Jane
Doe
Milk
2
John
Doe
Eggs
3
Eleni
Smith
Milk
1
Jane
Doe
Butter
Find Records With a Duplicate
Only returns records that have a duplicate.
ID
First Name
Last Name
Order
1
Jane
Doe
Milk
1
Jane
Doe
Butter
Highlight Records With a Duplicate
Highlights records that have a duplicate.
ID
First Name
Last Name
Order
1
Jane
Doe
Milk
2
John
Doe
Eggs
3
Eleni
Smith
Milk
1
Jane
Doe
Butter
Highlight Records Without Duplicates
Highlights all records that only occur once.
ID
First Name
Last Name
Order
1
Jane
Doe
Milk
2
John
Doe
Eggs
3
Eleni
Smith
Milk
1
Jane
Doe
Butter
Find Records That Occur Once
Returns records that only occur once.
ID
First Name
Last Name
Order
2
John
Doe
Eggs
3
Eleni
Smith
Milk
Highlight Records that Occur Once
Highlight records that have no duplicates.
ID
ID
First Name
Last Name
Order
1
Jane
Doe
Milk
2
John
Doe
Eggs
3
Eleni
Smith
Milk
1
Jane
Doe
Butter
This setting determines which column or columns uniquely identify a record. The fields that comprise the key are highlighted in blue in the two examples below.
Example: Unique ID consists of a single field: Last Name
| Columns for Unique ID |
Unique Records (duplicates removed) |
||||||||||||
| Last Name |
|
Example: Unique ID consists of a the combination of two fields: First Name and Last Name
| Columns for Unique ID | Unique Records (duplicates removed) |
||||||||||||||||
| First Name, Last Name |
|
When checked, all Rollup Fields may contain multiple values. This setting will concatenate all unique values in each Rollup Field.
Enabled: only when Show is set to Only Unique
Rollup Columns
All values in these fields will be displayed in the one record that represents all duplicates. Each value will be separated by the Rollup Separator character(s). Each value will be displayed only once; therefore, Rollup Fields will not have duplicate
Enabled: executed when Show is set to Only Unique and Rollup non-ID Fields is checked.Rollup Separator
Specifies the character that will be placed between each unique value in the Rollup Fields
Enabled: executed when Show is set to Only Unique and Rollup non-ID Fields is checked.Note: Click on
EXAMPLE
Setting
Value
Unique ID
Last Name
Rollup non-ID Fields
On
Rollup Fields
Order
Rollup Separator
,
Data set
ID
First Name
Last Name
Order
1
Jane
Doe
Milk
2
John
Doe
Eggs
3
Eleni
Smith
Milk
1
Jane
Doe
Butter
Report
ID
First Name
Last Name
Order
1
Jane
Doe
Milk,Eggs,Butter
3
Eleni
Smith
Milk