Filtering the Data Files at the Source to extract data, Using Alteryx Macro

(Click here for Japanese)

Background:

A straightforward way to filter data in Alteryx is by using the Filter Tool. However if the requirement is to pull just the specific data from multiple sources, then a macro might be very helpful. This post will give you a hint on how to start filtering the data at source. This is particularly helpful if the data comes from multiple large files.

How to achieve it:

Let us consider that all the input files are stored in the same network folder. For this post, I am going to consider four excel files each for a different year.

The aim of this post is to load data from a particular file specified by the user. First, let us start by connecting to all the files, followed by building a macro to meet the requirements. Use the input tool with the following configuration will connect with all the data files.

To build a macro start with a fresh workflow and place a macro input tool on the canvas. From the workflow configuration, make sure the macro is selected as a Standard Macro. Click on the macro input tool and then click on the “Edit Data” button to create the schema with sample data. Also make sure that “Show Field Map” is disabled for this example.

If ‘Show Field Map’ is enabled, it will list out every field that is in the Macro Input tool. The user then will have to specify all the input fields otherwise the macro will not run. On the other hand, by disabling this option the macro will be dynamic and the fields will be replaced with whatever fields are available in the input data.

The next step would be to add a Drop Down too. This allows the user to make input choices. Configure the Drop Down tool to allow “Manually set values” and add the desired values one per line.

Add four containers each with a filter as shown below and connect them with the Input macro tool. The filter would check for file name or data values, in this case it will search for the target year.

Add a Union tool and connect all the filter’s output to it, then use an Output tool as shown here.

The next step is critical, because it will allow the user’s drop down selection to be passed on to the filter container. So add an Action tool between the Drop Down tool and each container as shown below.

Configure each Action tool to be able to “Update Value with Formula”, the formula would check if the year is present in filename, if the year is absent in the filename then that container would be disabled and that file would be skipped. This process would continue for all available containers and only the target files would be loaded in the final output.

Finally an icon can be designated for this macro from the Interface Designer menu. Save and close this macro.

Now visit the original workflow which was created initially to connect all the files using an Input tool. The Input tool’s configuration is shared below for reference. By connecting the macro to this Input file, our requirements are met. The input choices would be displayed by clicking on the macro. Users can choose the desired year from its dropdown menu. Finally executing this workflow would give the specific results as shown below.

Summary:

In this post, it is clear that a macro can be used to choose only the specific input files to load data by choosing their target files.