De-duplicate
Description
De-duplicate is a powerful System calculation that helps you manage data duplication. Data duplication is common with LEFT OUTER joins and RIGHT OUTER joins. Recall that you do a LEFT OUTER join when you have a 1-to-many or many-to-many relationship between one table (the left table) and another table (the right table).
Calculation Editor
Options
| Input Parameter Values | Input Values | Required |
|---|---|---|
| Column to de-duplicate | Column or Literal | ✔ |
| Value if false | Column or Literal | ✔ |
| De-duplicate method | Distinct Values, First value in group, or Up to first level break | ✔ |
| Grouped column | Column | |
| Only Rollup If | Generic Criteria |
Remark
If you are using a Table Lookup calculation, there is an option to "Fetch Unique Targets Only Once" which simulates a de-duplicating of the output. See Table Lookup.
Example
Suppose you create a table join: F0411 left outer F0911 join. This is an example of a many-to-many relationship in JD Edwards:
Notice that when duplication is present in your query, you can usually see it immediately. Notice the AP Amount Dup duplicates within the Pay Items (001, 002, 003). Notice the G/L Amount Dup duplicates the pattern: 100.00, 100.00, 300.00 with each Pay Item.
Because of the many-to-many relationship, the highlighted amounts duplicate (and in two different ways). Although the 1,500 totals match, the value of 1,500 is in fact the wrong number. Now look at the AP Amount and G/L Amount columns. The AP amount de-duplicates its column (AP) to take just the first values in the group (which is correct for the LEFT table in the join). The G/L Amount de-duplicates its column by taking the values only up to the first level break (which is correct for the RIGHT table in the join).
De-duplicate is outlined on the following:
Column to de-duplicate (in the Example above AP Amount Dup, G/L Amount Dup).
Value if false: What value to use as a "filler" (e.g. blank or 0).
De-duplication method:
First value in group - Applies the first value in group once and uses the "filler" value for the rest.
Up to first level break - Applies all the values until the first group level break occurs. In the example above, the first level break occurs when the Pay Item goes from 001 to 002.
Distinct values - Applies a value if it is distinct within the group. Otherwise it applies the "filler".
Group column: If blank, the calculation uses the lowest level group when performing the de-duplication. If a grouped column is specified, the de-duplication will use that grouping as its reference for groups and level breaks.