How to dynamically join on non-empty fields

[Paulo1300]

Hi all! 

I have a table like below:

and am trying to join that data to data like this.. but ONLY join with the non-null fields from the table above:

So for example, if all 4 fields are filled in (left of the cost field), then I would want to join on all 4 fields. If only 3 are filled in then 3, and so on and so forth (and in priority of a 4 field join happens before a 1, 2, or 3 field join). Here is an example output table:

Any thoughts on how this can be accomplished? We are trying to input the data as a table rather than multiple lookup files that have to be managed (one at item level, one at vendor level, one at item-warehouse level, etc).

Also I know I can filter for each scenario and join - we are aiming to do this dynamically. So the ideal situation is the workflow sees the column headers in the left join input, and then joins with the right join input on only those fields.

I feel like this is a batch macro but I am having trouble figuring it out.

[MatthewO · Accepted solution]

@Paulo1300 attached is a sample workflow and a batch macro that would accomplish the dynamic join. That said, it will duplicate results based on your logic, so your Final Result table is not correct. For example, if your only match field in the right data set is the "Tools" Category, it will join to all instances of "Tools" in the Category field in the left data set.

[Paulo1300]

Thank you Matthew! Sorry for not updating the post, I assumed it was going to pass by unnoticed when it didn't get any action so I didn't update it -  but I figured it out as well! 

I actually posted a second thread on the duplication situation you mentioned here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/batch-macro-where-each-iteration-does-...

Someone helped figure that part out if you want to see! 

Sorry again for not updating the post and wasting your time!