Hi Paul,

Getting 30 rows seems to offer the best analytic experience in this case.  By joining on code ID I managed to create following view:

Are there specific reasons why you'd like to only have 10 rows?

Kind regards,

Wim

Hi Paul,

I see.  there are several ways to deal with this:

1. Group your data in your source: concatenate all the fields that multiplicate your data in one field.  Use calculated fields in Tableau ( CONTAINS() for example, to search for specific cases).

2. Blend your data: link the sheets Billing table and Coding Table, and add the same datasource again (data - new data source) to add the sheet Code Label Total.

For more information on blending: http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#multiple_connections.html 

Blending your data will result in multiple values for the same row, and show asterisks.

3. If you know you'll always have 3 rows for the same date entry, simply divide the hours by 3.  you can do this by double clicking a pill in the columns/rows shelves and adding /3. 

4. use Level of Detail calculations.  In a fully joined data source (30 records), the following resulted in the correct number of hours / employee:

sum({ FIXED [Date], [Client]: MIN([Hours])}).  This calculation looks per date and per client, then selects the minimum amount of hours (which is the same for all three entries for that client on that date) and takes the sum over the whole dataset.

Hope this helps!

Kind regards,

Wim

Hi Paul,

I just noticed option 4 can be simplified even further:

sum({ FIXED [Biling ID #]: MIN([Hours])})

This will check the biling ID (which should be unique) and return the sum of all the minimum values per billing ID. 

Using this calculation will allow you to create stuff like this:

If you're new to Tableau, Level of Detail calculations might seem complex (they kinda are), but they are really powerful tools.  I sugest looking into them if you're serious about using Tableau in the future.

Kind regards,

Wim

Hi Paul,

Follow the results that you need :

Hi Paul,

   I think it's better to do it in SQL rather than Tableau

You can use following query to get your data as you desire then you don't have to worry about LODs and Aggregations

Here is the Redshift code to get the way you like it

SELECT bt.*,ct.CODE_NAME, lt.LABEL FROM BILLING_TABLE bt

LEFT JOIN CODE_TABLE ct ON ct.CODE_ID = bt.CODE_ID

LEFT JOIN

(

SELECT CODE_ID,
  LISTAGG(CODE_LABLE,', ')
WITHIN GROUP (ORDER BY CODE_LABEL)
OVER (PARTITION BY CODE_ID) AS LABEL
FROM CODE_LABEL_TABLE
ORDER BY CODE_ID

) lt     ON lt,CODE_ID = bt.CODE

I know this is not Tableau solution but sometimes it helps to look out of Tableau!!

Good Luck!!

Suhas