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

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!!

Batch Macro

Join

Accepted answers

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

All comments

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

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

dreldrel

Maybe you can use field info to check if there are any duplicate columns in your data streams before you do joins. If so, you can preprocess your value and data to ensure you only have the column with the values you like after the join. If this helps, please like the comment

peyton-brooks

Thank you both for your feedback and pushing me to dive deeper vs. resulting to a macro. I decided to go through the field list and drop off the "left" columns and keep the right columns only. I have experienced a similar frustration since and I utilized the multi-field formula and dynamic select tool in that scenario to help name all the fields i wanted to keep and then filter them with the dynamic select. Thank you for ya'lls help!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703