Join different sets of data

amrutkasyap

Hello,

I need help in joining these records and creating the correct number of records. I have listed the steps below.

1.  Duplicate the first set of data and rename the record status to - 'Check'. This becomes third set of data
2. Based on Key and MAT, copy only Seller information from Second set of data to third set of data. All the other info will be from first set of data
3. Combine all these data to get a output of 12 rows.
4. The columns are the same across all sets of data. There are 50 other columns have not listed all the other columns.

I am getting twice the number of rows in the final output. I have tried using unique tool but unable to get correct output. Any help is appreciated.

Thanks

First set of data

MAT	Type	Key	RecordStatus	Date	Seller	Seller_City	Seller_Num	Seller_Name	Seller_State	Ship-To City	Ship-To Name
1014061	60	1881	DEST	1/1/2001			3716	Company		LOS ANGELES	LLC
1014061	60	1881	DEST	1/1/2001			3716	Company		LOS ANGELES	LLC
1014061	60	1881	DEST	1/1/2001			3716	Company		LOS ANGELES	LLC
1014061	60	1881	DEST	1/1/2001			3716	Company		LOS ANGELES	LLC

Second set of data

Num	Type	Key	RecordStatus	Seller	Date	Seller_City	Seller_Num	Seller_Name	Seller_State	Ship-To City	Ship-To Name
1014061	10	1881	ORIGIN	5039	7/7/2007	FORT WORTH	2016	AA LLC	TX
1014061	10	1881	ORIGIN	5039	7/7/2007	FORT WORTH	2016	AA LLC	TX
1014061	10	1881	ORIGIN	5039	7/7/2007	FORT WORTH	2016	AA LLC	TX
1014061	10	1881	ORIGIN	5039	7/7/2007	FORT WORTH	2016	AA LLC	TX

Third set of data

MAT	Type	Key	RecordStatus	Seller	Date	Seller_City	Seller_Num	Seller_Name	Seller_State	Ship-To City	Ship-To Name
1014061	60	1881	CHECK	5039	1/1/2001	FORT WORTH	2016	AA LLC	TX
1014061	60	1881	CHECK	5039	1/1/2001	FORT WORTH	2016	AA LLC	TX
1014061	60	1881	CHECK	5039	1/1/2001	FORT WORTH	2016	AA LLC	TX
1014061	60	1881	CHECK	5039	1/1/2001	FORT WORTH	2016	AA LLC	TX

Final Output

MAT	Type	Key	RecordStatus	Seller	Date	Seller_City	Seller_Num	Seller_Name	Seller_State	Ship-To City	Ship-To Name
1014061	60	1881	DEST		1/1/2001		3716	Company		LOS ANGELES	LLC
1014061	60	1881	DEST		1/1/2001		3716	Company		LOS ANGELES	LLC
1014061	60	1881	DEST		1/1/2001		3716	Company		LOS ANGELES	LLC
1014061	60	1881	DEST		1/1/2001		3716	Company		LOS ANGELES	LLC
1014061	10	1881	ORIGIN	5039	7/7/2007	FORT WORTH	2016	AA LLC	TX
1014061	10	1881	ORIGIN	5039	7/7/2007	FORT WORTH	2016	AA LLC	TX
1014061	10	1881	ORIGIN	5039	7/7/2007	FORT WORTH	2016	AA LLC	TX
1014061	10	1881	ORIGIN	5039	7/7/2007	FORT WORTH	2016	AA LLC	TX
1014061	60	1881	CHECK	5039	1/1/2001	FORT WORTH	2016	AA LLC	TX
1014061	60	1881	CHECK	5039	1/1/2001	FORT WORTH	2016	AA LLC	TX
1014061	60	1881	CHECK	5039	1/1/2001	FORT WORTH	2016	AA LLC	TX
1014061	60	1881	CHECK	5039	1/1/2001	FORT WORTH	2016	AA LLC	TX

MichalM

@amrutkasyap 

Based on what you described the attached should work. I made some assumptions so please let me know if anything doesn't make sense

join-data.yxmd