Multiple Inputs - Vlookup from each separate report

[lisamccullough]

I have multiple input reports, about 8 separate reports. Each report has one column of data that is similar - Companies. I can add a Select Tool to determine what I need from the first report, but how do I perform a VLOOKUP on the other reports to pull the data I need from each report?

Example: input reports are underline and the columns headers are listed under

DED

Companies

Trees

Dollars

Tier Report

Sales

Companies

Recount

Assets

Balance Sheet

Companies

Revenue

Liabilities

Accruals

Companies

No Sales

My final report should have the following columns:

Companies   Trees   Dollars  Sales  Balance Sheet   No Sales

I'm taking three columns from the first report and only one from the others. Please let me know if I should send a mock workflow.

I also need to include all separate input reports on a separate tab within the same Excel spreadsheet.

Thanks in advance.

[GaneshBo]

Hi @lisamccullough ,

You would use the Join Multiple tool for this. Within the Join Multiple tool, there is an embedded "Select" function, in which you can select which columns to output. 

I have attached a sample workflow for your reference. Hope this helps!

Best,

Ganesh

[AbhilashR]

Hi @lisamccullough, based on how I understand your problem statement, you will need to join all the input tools on a  common column  - in your case Companies. You could use the Join Multiple tool to do this operation at one go.

If you run into any challenge, go ahead and provide us some dummy input data and one of us would be more than happy to build you a working prototype.

I hope this helps!

[atcodedog05]

Hi @lisamccullough ,

Adding to the above points Join Multiple Tool is a great optimized option. But it might default will output a inner join or outer join which is not favorable in all scenario.

VLookup of excel is a left Join scenario.

You can use Multiple Join tools with Union Tool like below example. Which will give you more control over the flow and the Left & Right joins. PS: we might have to do Left Join  if Main data is on left and we dont want any leakage and at the same not all values from the lookup. 

Refer the link to for the example.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Joining-multiple-excel-worksheets/td-p...

Here is a interactive lesson on Joins.

https://community.alteryx.com/t5/Interactive-Lessons/Joining-Data/ta-p/76634

and Union

https://community.alteryx.com/t5/Interactive-Lessons/Blending-Data-with-Unions/ta-p/76632

Union L & J anchor like in the image.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[grazitti_sapna]

Hi @lisamccullough , join multiple tool would be a better approach to your problem rather than using join tool multiple times.You can select and deselect the fields in the multiple join tool as we do it in the join tool.

I have mocked up a workflow for your reference.

Query- I also need to include all separate input reports on a separate tab within the same Excel spreadsheet.

For this you can use output tool for all the reports separately and change the sheetname of the workbook in excel in this way you can have your multiple inputs in single excel.

Thanks.