-
1. Re: Getting Duplicate Entries with 1 to Many Relationship
Wim Kegels Jan 12, 2017 5:28 AM (in response to Paul Heering) -
2. Re: Getting Duplicate Entries with 1 to Many Relationship
Paul Heering Jan 12, 2017 5:35 AM (in response to Wim Kegels)Wim,
Thanks for responding.
It might be the wrong approach but I was trying to get 10 rows was because the main metric here is hours. If have 3 rows per billing entry the aggregate numbers for hours are off (tripled).
So if I try to look at how much Paul billed on January 1st it will tell me 9 but it was only 3.
Paul
-
3. Re: Getting Duplicate Entries with 1 to Many Relationship
Wim Kegels Jan 12, 2017 6:08 AM (in response to Paul Heering)2 of 2 people found this helpfulHi 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
-
4. Re: Getting Duplicate Entries with 1 to Many Relationship
Paul Heering Jan 12, 2017 6:18 AM (in response to Wim Kegels)Wim,
Thanks for the detailed and speedy response.
Based on your ideas.
Option #1--I have no control of the source data. In the real life example, I am connecting to redshift data. I just made up these excel sheets to simplify the data and take out private information.
Option #2--I think this might be the way to go, I will try it out.
Option #3--I almost typed it in my original post but the number of labels varies, it can be anywhere between 1 and 9. So I can't just divide by 3. Although, I wish I could, that seems like the simplest solution.
Option #4--This might work too. Just wondering what would happen if you met with the same client twice in one day? Would that calculation still work? So for example if Paul (staff) met with John (client) twice on Monday so there are two billing entries in the original source. Both appointments were for 1 hour. Now we would end up with 6 rows (3 rows each for each original row). Would the aggregate number be 1 (pulling in minimum) or would it somehow know that it should be 2?
Thanks again for all your help. I'm new to the forums so I am not sure the best way to give "points" or credit for helping but I will do my best.
Paul
-
5. Re: Getting Duplicate Entries with 1 to Many Relationship
Wim Kegels Jan 12, 2017 6:26 AM (in response to Paul Heering)1 of 1 people found this helpfulHi 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
-
6. Re: Getting Duplicate Entries with 1 to Many Relationship
Veronica Simoes Jan 12, 2017 6:45 AM (in response to Paul Heering)4 of 4 people found this helpful -
7. Re: Getting Duplicate Entries with 1 to Many Relationship
Paul Heering Jan 12, 2017 10:46 AM (in response to Veronica Simoes)Thanks Veronica, sorry I didn't give more details, but I don't think that solution works because when I try to make a chart of these data I am still having the issue where it adds them all up, for 1/1/17 it says Paul did 9 hours if I put sum of hours on rows and it says Paul did 1.5 hours if I put average of hours on rows. Neither is correct. He provided 3 hours of services on 1/1/17 (one appointment for 1 hour and another for 2 hours).
I'm betting the issue is that I am just implementing your solution incorrectly but I can't get it to solve the problem I'm having.
Paul
-
8. Re: Getting Duplicate Entries with 1 to Many Relationship
suhas doke Jan 12, 2017 1:14 PM (in response to Paul Heering)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