3 Replies Latest reply on Jul 25, 2016 12:30 AM by Tableau kumar

# Record data duplicate when join 2 sheet

dear everybody.

I have 2 sheet, 1 sheet have quantity product of daily. 1 sheet have detail defect product of daily by section & type of defect.

when I join 2 sheet with key : date.

quantity product duplicate many with quantity defect by daily

=> when I sum(quantity product) _daily = (quantity product )x (quantity defect)  => wrong result

I also make 1 calculate field = sum(quantity product)/count(date)

result of daily is correct, but result of monthly is wrong.

who can help me ?

thank so much.

sorry, I can't upload any field.

 Date Q'ty product 1-1-2016 0 1-2-2016 3699 1-3-2016 0 1-4-2016 4204 1-5-2016 3568 1-6-2016 3606

 date Section Cause Department Defect Quantity defect 1-2-2016 A Part Damage asdg 1 1-2-2016 A Part Damage asg 1 1-2-2016 A Part Damage w 1 1-2-2016 A Part Damage e 1 1-2-2016 B Wrong Assembly asg 1 1-2-2016 B Defect Part Delivery e 1 1-2-2016 A Part Damage awt 1 1-2-2016 B Part Damage sad 1 1-2-2016 A Part Damage er 1 1-4-2016 A Part Damage weg 1 1-4-2016 B Part Damage ad 1
• ###### 1. Re: Record data duplicate when join 2 sheet

hi  Duong,

So I assume you know why it is creating duplicate records? if not post back here and I can explain.

Now onto the solution...so I assume you need both Item and Defect type for your analysis. So this means you have date a different levels of grain (or granularity), and this is where data blending is ideal for this task. Below is a link to a blog on exactly this problem

Hope it helps, and makes sense, but let me know if you have any other queries.

1 of 1 people found this helpful
• ###### 2. Re: Record data duplicate when join 2 sheet

Dear Mr. Simon

thank so much , this is thing what I need now.

my company has just buy tableau, I'm begin of tableau

thank so much again.

• ###### 3. Re: Record data duplicate when join 2 sheet

As Simon Runc  suggested, You have to add multiple joins between (Among) tables.

For an instance

Table 1 having Country, Sales fields (Different countries have different currencies)

Date                    Country     Currency     Sales

01-Jan-2016          India          INR               200

01-Jan-2016          USA          \$                    500

01-Jan-2016          UK            #                    1000

02-Jan-2016          India          INR               200

02-Jan-2016          USA          \$                    500

02-Jan-2016          UK            #                    1000

03-Jan-2016          India          INR               200

03-Jan-2016          USA          \$                    500

03-Jan-2016          UK            #                    1000

Assume, we have another table that is having Currency conversation rate. (Converting into USD)

Date                    From_Country     To_Country     Conversation Ratio

01-Jan-2016          USA                    IND               1/60

01-Jan-2016          USA                    UK                1.5

01-Jan-2016          USA                    USA               1

02-Jan-2016          USA                    IND               1/65

02-Jan-2016          USA                    UK                1.55

02-Jan-2016          USA                    USA               1

If we want to show the data in USD, We have to define the joins at Country level and Date level since Conversation rate is vary from date to date as well as country to country.

[Table1].[Country] = [Table2].[To_Country]

and

[Table1].[Date] = [Table2].[Date]

If we map Country' only, we dont get right figures.

I hope this is crystallized you.

Best Regards

Kumar