5 Replies Latest reply on Mar 31, 2017 6:26 AM by Jennifer Brown

# Question re: how to disregard duplicate records in source database

Hello!

I am currently working with a data set that includes multiple records for a
course in which students enroll, and am trying to figure out how to isolate the
hours in the course for only one instance of the course.  For example,
assume there are 4 records for Econ 1.  Each record shows that the class
meets for 4 hours.  However, since it is the same class, if I add hours
for all four records, my output will show the course meeting for 16 hours
instead of 4.  One approach to solving this would be to sum all the hours
and divide by the number of records (e.g.,[SUM OF HOURS] / [COURSE NAME] where
the course name appears four times.  However, [COURSE NAME] is a
dimension, and [SUM OF HOURS] is a measure.  I cannot figure out how
to divide a measure by the sum of distinct records within a dimension.  (I
have in the past used COUNTD but I understand Tableau no longer supports that
function.

I have attached a packaged workbook. Relevant fields in my attempt so
far are as follows:

[C.Sum Hours by Unique Mtg Room and Time] -- calculated field as
follows:  sum([Mtg Hrs Per Week])/ ([Unique Physical Meeting])
[This is not a valid calculation because it is dividing a measure by a
dimension.

Unique Physical Meeting -- the ID assigned to each record that meets at the
same time in the same place each week.  Each record has a number of
meeting hours associated with it.  As
with the example above, there could be 4 Unique Physical Meeting records that
are exactly the same, each with total meeting hours for the class.  To get the correct meeting hours, we need to
sum meeting hours for all 4 records, and then divide by the number of identical
records.

The relevant worksheet in the packaged workbook is entitled “Southeast.”

Any thoughts about how to approach this problem, either based on the initial
solution outlined above, or any other method, would be greatly appreciated.

Thank you!

• ###### 1. Re: Question re: how to disregard duplicate records in source database

Either you have to differentiate those rows with an additional dimension (such as date-time entered, for example), and then grab the first one using the unique value (MIN([Date-time field]), for example), or instead of doing SUM([Hours]), do AVG([Hours]).  (Assuming the hours value for each row is the same.)

You also have a [Number of Records] measure that Tableau creates for you.  You can use that to determine the number of rows in a given course, and then do your own average calculation.

So:

SUM([Hours]) / SUM([Number of Records]) would be evaluated at the [Course Number] level if you have [Course Number] on the sheet.

• ###### 2. Re: Question re: how to disregard duplicate records in source database

Jennifer,

I think the issue you are facing has to do with the database connection you are using.  It looks like you are using a "legacy" connection, and that has limited functionality in Tableau.  For example, Tableau certainly has the COUNTD function, but not for legacy connections, apparently.  If there is some way you can change to a regular connection, rather than legacy, I think you will get all of the functionality of Tableau back again, and you should be able to do much more.

Kaz.

• ###### 3. Re: Question re: how to disregard duplicate records in source database

Jennifer,

Here is a Level of Detail (LOD) Expression that I think gives you what you're looking for, even if [Class Title] is not part of the view's level of detail:

I tested it in isolation and it seems to behave as you would want it to. I also dropped it into your Southeast view, but I can't tell for certain that it's yielding the results you were anticipating. Let me know.

NOTE: Your data sources are legacy Excel files, which do not support LOD Expressions. I had to create an extract of your data to enable LOD Expressions.

Workbook attached (version 10.1).

Let me know if this is what you're looking for!

• ###### 4. Re: Question re: how to disregard duplicate records in source database

Kaz,

Thanks for the help! I tried not using the legacy connection, and the

countd function reappeared. Much appreciated!

Jen

On Wed, Mar 29, 2017 at 5:17 PM, Kaz Shakir <tableaucommunity@tableau.com>

• ###### 5. Re: Question re: how to disregard duplicate records in source database

Jamieson,

That didn't quite eliminate the issue of duplicate records, but it has

been very helpful! As my boss and I tried to solve the issue, LOD

calculations have been essential (so thank you!). I'm not sure where we

are right now (haven't chatted with my boss yet today), but I might check