Multiple Inputs - Vlookup from each separate report
- 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
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
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 😀👍
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
