Join Duplicate issue

[NiranjanK1]

Hi All,

Im unable to join two tables with the data im getting un expected blanks and duplicate records.

Table1		Table2
Year	Month	Week	Region	A1	A2	A3		Year	Month	Week	Region	T1	T2	T3	T4
2022	1/1/2022	Week 1	APAC	ETY	79	10		2022	1/1/2022	Week1	APAC	2745	248	0	0
2022	1/1/2022	Week 1	EMEA	ETY	93	62		2022	1/1/2022	Week1	EMEA	8768	14836	2328	996
2022	1/1/2022	Week 1	NAEAST	ETY	197	40		2022	1/1/2022	Week1	NAEAST	4677	1284	872	3573
2022	1/1/2022	Week 1	APAC	BDS	5	10		2022	1/1/2022	Week2	APAC	727	452	0	0
2022	1/1/2022	Week 1	EMEA	BDS	56	115		2022	1/1/2022	Week2	EMEA	6144	11410	1705	461
2022	1/1/2022	Week 1	NAEAST	BDS	11	273		2022	1/1/2022	Week2	NAEAST	4015	1270	808	2265
2022	1/1/2022	Week 2	APAC	ETY	45	6		2022	1/1/2022	Week3	APAC	956	976	0	0
2022	1/1/2022	Week 2	EMEA	ETY	56	30		2022	1/1/2022	Week3	EMEA	5881	14064	2762	696
2022	1/1/2022	Week 2	NAEAST	ETY	156	36		2022	1/1/2022	Week3	NAEAST	2522	1176	617	1913
2022	1/1/2022	Week 2	APAC	BDS	11	15		2022	1/1/2022	Week4	APAC	997	697	0	0
2022	1/1/2022	Week 2	EMEA	BDS	55	5		2022	1/1/2022	Week4	EMEA	6003	14869	1591	797
2022	1/1/2022	Week 2	NAEAST	BDS	13	56		2022	1/1/2022	Week4	NAEAST	3675	1152	909	2874
2022	1/1/2022	Week 3	APAC	ETY	50	17
2022	1/1/2022	Week 3	EMEA	ETY	86	57
2022	1/1/2022	Week 3	NAEAST	ETY	138	49
2022	1/1/2022	Week 3	APAC	BDS	8	5
2022	1/1/2022	Week 3	EMEA	BDS	154	102
2022	1/1/2022	Week 3	NAEAST	BDS	14	107
2022	1/1/2022	Week 4	APAC	ETY	63	12
2022	1/1/2022	Week 4	EMEA	ETY	54	61
2022	1/1/2022	Week 4	NAEAST	ETY	203	57
2022	1/1/2022	Week 4	APAC	BDS	18	9
2022	1/1/2022	Week 4	EMEA	BDS	103	76
2022	1/1/2022	Week 4	NAEAST	BDS	16	73

Thanks & Regards,

Niranjan

[JamesCharnley · Accepted solution]

Hi @NiranjanK1 

Generally speaking when you're getting duplication after joins it means that you're not joining on enough unique identifiers. I'd expect in this join you'd want to join on Year, Month, Week and Region to get A1, A2, A3, T1, T2, T3, T4 on a row level with no duplication.

The rows need to be formatted consistently too. In your example there is a space between the week and the number in table 1, but no space in table 2, so those wouldn't joined without that being corrected first.

Does that help?

[NiranjanK1]

@JamesCharnley Thanks a lot it is working as expected. Quick doubt, If I want to remove duplicate from second table what is the best way. 

[JamesCharnley]

@NiranjanK1 not entirely sure what you mean in this instance since there isn't any duplication as such in your table 2 or the join output here (there's nothing to distinguish between BDS and ETY in table 2, not sure if that's relevant to you here).

But on a higher level if you have duplicated rows for whatever reason you can remove them with a Unique tool, where you can select column names whose combination you want to be unique (though be wary in some instances because it will take the first row). Check out the documentation here: https://help.alteryx.com/20223/designer/unique-tool