Eliminate Duplicates in Impromptu

[cogdev]

Can someone explain what is the key benefit of this check box?
Is there a (wrong) reason why duplicates would show up?

 

[DoubleD]

If you want to export your data to CSV of Excel, and your data is grouped, the detail data will show up in the export unless you check Eliminate Duplicates.

Pain is stress leaving the body.

DoubleD

 

[cogdev]

So is that the only reason? I am seeing a difference in my row-counts in Impromptu when I turn it on or off. So I guess it is affecting the Impromptu data.

 

[DoubleD]

Exactly.
With Eliminate Duplicates OFF, your row count will be the total number of detail records.
With Eliminate Duplicates ON, your row count will be the total number of unique records

Pain is stress leaving the body.

DoubleD

 

[griffindm]

cogdev,

A little more detail on what DoubleD is saying:

Say you create a report that pulls in partial information on a transaction, such as date, account, and amount, As in:

  Date     Account     Amount
1/1/04     100-100-001    $100.00
1/1/04     100-100-001    $100.00
1/1/04     100-100-001    $100.00
1/2/04     100-100-001    $125.00
... and so on.

The three leading rows are indicative of similar transactions (same date, account, and amount) but have some other attribute (vendor name, employee id, etc) that distinguishes them. The report without the eliminate duplicates checked would agree to the totals of the amounts posted. With the checkbox selected, only the first transaction of the first three would appear in your report, and the totals would NOT agree.

In short, do not use this checkbox unless you know WHY you need it.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DrSimon]

I agree with Dave. People seem to use this checkbox as a panacea for dealing with replicates with joined tables. Unless it is a one-off problem, a better solution is either to correct the joins in the underlying tables or create alias tables with the correct joins. You have seen the difference in rows counts; with more complicated summary functions there is a danger of getting the wrong answer if duplicates are eliminated.
Simon Rouse

 

[cogdev]

Great stuff guys, I agree with the panacea arguement, I myself pretty close to doing so. But them I decide to visit tek-tips.com, now I know better, much better.

 

[DoubleD]

I agree with griffindm.

do not use this checkbox unless you know WHY you need it.

I disagree with DrSimon's statement.

Unless it is a one-off problem, a better solution is either to correct the joins in the underlying tables or create alias tables with the correct joins.

With Impromptu's ability to export data to Excel and/or CSV, there are many reasons to export Summarized data to spreadsheets. There is nothing wrong with this, and it doesn't mean the joins are set up wrong. DrSimon, I'm sure you didn't mean to suggest that Eliminate Duplicates should never be used, but I want to be sure people realize there are many valid reasons to use it.

Pain is stress leaving the body.

DoubleD

 

[DrSimon]

DoubleD I don't think we disagree at all. Of couse there are reasons for using the flag and your quote from Dave says it all. I have just seen too many people, including commercial professionals using it without thought, causing havoc to others. There should be campaign for the correct use of the Eliminate Duplicates flag!
Simon Rouse