25 October 2016
Wildcard Unions are one of the most powerful everyday features of Alteryx, allowing users to process a huge number of files quickly with just one tool. However, they require all inputs to have an identical column structure in order for Alteryx to process the files together. They also struggle to deal with more distinct data sources, such as XML files, that require preparation before they can be unioned together.For a one-off task, a wildcard union may be the simplest solution but for long-term repeated processing, a batch macro may be the most efficient way to prepare your data. Batch macros can deal with minor issues such as columns in different positions and, in the case of data sources that need a little TLC before unioning, can prepare each file individually before unioning them together.However, what happens if any of your ‘identical’ data sources don’t contain every column? For example, perhaps not every department in your company records the ship mode of a customer’s order. If your macro contains a formula or transformational tool, such as summarise or transpose, in which the rogue column is included, your entire macro will fall over as it fails to find said field.In order to combat this, we simply want to add this additional column with null values as a new field in the workflow and then union it to the existing field – easier said than done! However, thankfully Alteryx CTO Ned Harding has already fixed this for us. He created a macro which allows us to generate new fields and bring them into our workflow. More details and a link can be found here.Simply add this macro into your workflow prior to doing any heavy downstream processing and list the names of the fields you want to add, along with the type.[caption id='attachment_8938' align='alignnone' width='499']
Click the image to enlarge[/caption]As with Ned’s sample workflow, add a text input tool before the macro and add two columns and one row of sample data, to activate the connected macro.[caption id='attachment_8927' align='alignnone' width='500']
Click the image to enlarge[/caption]The output should then be unioned with the main workflow and added to the bottom, either by connecting it as #2 or by specifying the order of the files within the union tool.[caption id='attachment_8928' align='alignnone' width='500']
Click the image to enlarge[/caption]Once you have run this you will have a completely blank row at the bottom of your dataset and two new columns, generated by the text input, which you can remove with a filter or sample tool and a select tool, respectively. Hey presto, you can now run your batch macro and you won’t have to worry about any pesky missing fields causing your workflow to fail!Thanks for reading, please get in touch if you have any comments or feedback. Follow me on Twitter @robsuddaby.
Click the image to enlarge[/caption]As with Ned’s sample workflow, add a text input tool before the macro and add two columns and one row of sample data, to activate the connected macro.[caption id='attachment_8927' align='alignnone' width='500']
Click the image to enlarge[/caption]The output should then be unioned with the main workflow and added to the bottom, either by connecting it as #2 or by specifying the order of the files within the union tool.[caption id='attachment_8928' align='alignnone' width='500']
Click the image to enlarge[/caption]Once you have run this you will have a completely blank row at the bottom of your dataset and two new columns, generated by the text input, which you can remove with a filter or sample tool and a select tool, respectively. Hey presto, you can now run your batch macro and you won’t have to worry about any pesky missing fields causing your workflow to fail!Thanks for reading, please get in touch if you have any comments or feedback. Follow me on Twitter @robsuddaby.