Trying to join to data sets the second have new columns with related cells to the first

[Motivator]

Hi, Please can someone point me in the right direction. I have 2 data sets:

Data 1 : Has the individual persons record of which company they work for.

Data 2 : Has the Continent where those companies have offices/or operate in.

When we want to market about a product relevant to a particular Continent, we want to be able to select to the individuals within a company that operate there?

Hope that makes sense, I have put together an example (the "output file" shows what I am looking for)

Thanks

[echuong1]

Would you be able to upload copies of your input files or upload a packaged worfklow? A .yxmd file is just the workflow, it doesn't contain any data.

To package a workflow go to Options > Export workflow.

[Motivator]

HI, Thanks for the response. I apologise I thought the file I uploaded would include the data.

New export attached, thanks

[echuong1 · Accepted solution]

You can use the transpose tool to put your country data in a more vertical format for the join. The join will bring together the countries, companies, and people. After that, you can use a crosstab tool to put the countries as new headers. To get the original company name back into the population, you can use a join on name.

See attached. Let me know if this works for you!

[Motivator]

Thanks so much for that, however it appears that your example is in a newer version of Alteryx and I can't view it.

I have 

Are you able to save it as an older version or are you able to show me screen shots of the transpose and join configs?

Thanks

[echuong1]

I cannot - even if I downgrade on my end it will change to the newest version again.

If you go to the .yxmd file, open it using a text editor like notepad. In the XML, update the version number on line 2. 

[Motivator]

Thanks you so much, you are a star! I will register as the solution.