This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email email@example.com for assistance.
This article is part of the CS Macro Development Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros.
So let’s say you have a dataset and you want to keep the order of the fields the same as it goes through your data blending or analytical process. Most of the time you may not have to worry about field orders changing. But let’s say you have to split the data up for any reason and join it back together. Normally, your instinct would be to use a select tool in order to manually reorder the fields. But say you have 100 fields, this could be a very time-consuming and annoying process.Also, say you had a macro that requires these field orders to be updated dynamically. You wouldn’t be able to use a select tool at that point.
Luckily there is a neat little trick you can do using the Sample and Union tools.
Let’s take a look at a simple example:
Here we have a dataset with four fields. We selected two fields which lets say hypothetically undergo some change but have kept the same field names.
Now we join them back by record position in the original dataset using the Join tool. You know that Alteryx will by default rename any fields coming in the “Right” input with a “Right_” as a prefix if have those fields share the same name as any fields in the “Left” input.
Since you want the new fields overwriting your old fields you want to make sure the original dataset goes into the “Right” input while your modified fields go into the “Left” Input. The main reason you want to do this is because in the join tool you can deselect all duplicates automatically by going into Options->”Deselect Duplicate Fields.
Note: If you wanted to do this in a batch macro you have to attach a Dynamic Select tool after the joined data from the Join tool. Then, you have to select “Select via a Formula” in the drop down and ‘type !StartsWith([Name],"Right")’ in the “Expression” box to dynamically deselect duplicate fields.
Now that your fields are updated you notice that your data is not in the order it was originally.
To get this data back into the order it was previously, we attach a Sample tool to the original dataset and select “First N Record" and N=0. This will give you the field headers in the proper original order.
Connect the output of this first and then output of your Join to a Union tool and select "Auto Config by Name". The output order of the connections matter. The connection coming from the sample tool must be the first connection to the Union tool.
Now your fields will be back in their original positions!
If new fields were added before the Join. The Union tool will default to putting those fields at end.
The example below was built in Alteryx version 10.1.