Consolidation of data sets using thee combination of keys

[farhana_91]

Hi All,

I am currently facing an issue where I have three files to be consolidated. 

The combination of the columns I am looking for is:

Client Region Project ID Project

Now the three files have these common ID's.

However, data Set 1 has :

Client	Region	Project ID	Project
A	IMEA	101	Apple
A	IMEA	102	Apple
A	Europe	101	Apple
B	IMEA	201	Orange
B	IMEA	202	Orange
B	Europe	201	Orange

Dats Set 2:

Client	Region	Project ID	Project
A	IMEA	101	Apple
A	IMEA	101	Apple
A	IMEA	102	Apple
A	IMEA	102	Apple
A	Europe	101	Aple
B	IMEA	201	Orange
B	IMEA	202	Orange
B	Europe	201	Orange
B	Europe	201	Orange
C	US	300	Kiwi

Whereas Client -C is not a part of Data Set 1&3

Data Set 3:

Client	Region	Project ID	Project
A	IMEA	101	Apple
A	IMEA	102	Apple
A	Europe	101	Apple
B	IMEA	201	Orange
D	Australia	401	Jackfruit
E	China	502	Pineapple

Where as E is not there in Data set 1 & 2.

But my result should contain all the data with their measure as attached in the excel file.

Thanks in advance for you help.

Regards,

Farhana

[Claje · Accepted solution]

The Join and Union tools are your friend for this kind of design.

I've attached a workflow that I think covers all your requirements.  Note that I made some assumptions about Data Set 2, and I also have 9 results instead of 8 (your example output did not have a "D" line).  I also fixed a typo in Data Set 2 as it was throwing off my results.

Hope this helps!