Populate blank fields on left side of join with corresponding fields on right side

[peyton-brooks]

Hey!

I am  joining multiple files together and having to do multiple joins to accomplish different criteria each round.  Does anyone have a batch macro created to run through the specific fields on the left side that have corresponding Right column names and populate the left side fields if they are empty?  I am very new to building macros and I was not able to build one out and I am spending way too much time on this!  I have a .xlsx file that has the field names that would exist on the left without the "Right_" prefix that I could use as a control if that's helpful... Appreciate the help! 

Example - Joined 2 sets of data (Left and Right) based on Project # on left and Project # on right - in the "J" anchor, I now have a field "Org Name" and "Right_Org Name". If "Org Name" is empty and "Right_Org Name" is not, I want Alteryx to populate "Org Name" with Right_Org Name".  I will then set up a select tool to drop the "Right_" columns to clear out some noise in my workflow.

Thanks!! 

[caltang]

If the number of fields are the same, it would be easier to pivot the columns to rows and then join them via a key, then use a formula tool to get what you want -> if left is empty take right etc. 

Dont think you need a macro for this. If you have some sample data we can show you better

[KGT]

I can't think of the dynamic way off the top of my head, but if I was building this, then I would transpose, join, formula and then crosstab back. You may choose to filter and only get the ones where the left is missing info first.

Otherwise, create a lookup table of the details, and just replace the whole set... Is it possible that there is an org filled out for instance that would be different from the one on the right? If not, then why are you even trying to preserve the left fields, just replace them all in the join (deselect, and then Options < Clear All renames).

Depending on how many missing values etc as to which is easier. A Batch Macro sounds like overkill, but maybe it is best in your situation....

• Otherwise, you could join, then use the Arrange tool to create 2 columns, one for left fields and one for right fields, then formula, and crosstab back.
• A dynamic formula tool (CReW MAcros) would do this simpler, as you could programmatically write all the formulas then pass them in.
• If you are comfortable with XML, then you could programmatically write all the formulas in the XML syntax and then paste them into the formula tool backend (you want to know what you're doing here though).
• A batch macro for this would have Fieldnames as the control parameter going to a formula twice (one for the fieldname, one for Right_fieldname configured in Action tool). And the formula would be as simple as IIF(IsNull(),Right_fieldname,fieldname).