CS Macro Dev: Preserving Field Order without Using the Select Tool

[Ozzie]

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.

[Hiblet]

This really helped me.  I had a macro that re-ordered fields, and this nicely shows how to fix that.  It makes the whole thing just a little more professional.  Many thanks!

[BonusCup]

@Ozzie 

Thanks so much for this.  I had an issue with Dynamic Fields changing order after a Join/Append.  This helped with that issue.