4 Replies Latest reply on Sep 24, 2014 8:42 AM by bruce lomasky

# how to get count of distinct trip #s, based on count of another column

My table has 2 columns, tripNumber and routeNumber, the primary key is the 2 columns, so each tripNumber has 1 to 8 rows, so it looks like this

1   10

1   12

1   14

1   16

2   10

3   12

3   16

Simple, right?  Here is what the customer is asking for:

1) show (graph) the # of trips with 1 route, with 2 routes, with 3 routes, etc.

2) for every route (there are about 12 routes) if there is a trip with multiple routes, show the other route and the # of times it is on the same route, so with this data, Route 12 would display alt route 10 with a count of 1 and 16 with a count of 2

thanks in advance, since my tired brain is not sure how do do this in Tableau

Bruce

• ###### 1. Re: how to get count of distinct trip #s, based on count of another column

Hi Bruce - I understand most of what you mean and might have a few ideas. I have a bit of confusion about your requirement #2, though. Can you elaborate? specifically on the below part:

Route 12 would display alt route 10 with a count of 1 and 16 with a count of 2

• ###### 2. Re: how to get count of distinct trip #s, based on count of another column

Sorry if I was not clear, let me try again.  since route 12 is on trip 1 and trip 3, I would want to get the other trip numbers on the same route, and get a count of the number of times each one occurs, so on trip 1, besides, 12 there is route 10, 14 and 16, and on route 3 there is route 16, so for route 12, I would like to display 10 with a count of 1, 14, with a count of 1 and 16 with a count of 2 (since 16 is on 2 diff trips, or, in other words, I would like a count Distinct (tripNumber) for other routes sharing the same trip number

Does this help?

Thanks

• ###### 3. Re: how to get count of distinct trip #s, based on count of another column

Here is an attempt that may work for you, or at least give you a start.

I used a custom SQL query to do a self-join and create combinations.

Then I did a calculation on the other side to filter out just the alternate routes,

and finally did a count. It seemed to give the right results.

Attached is the data file I used and the twbx.

The custom SQL could surely be improved upon,

and I'm not completely sure if the Index column I'd added to the data was necessary.

• ###### 4. Re: how to get count of distinct trip #s, based on count of another column

Thanks, I appreciate the fast response.  I will check it out this weekend. (you know the old, "clear the alligators from the swamp!")

Bruce