Consolidation of data sets using thee combination of keys
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Input
- Join
- Preparation
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!