How to dynamically join on non-empty fields

Question

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 Answer

@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.