Mapping Fields to Defined Template

[MT1985]

To start from the beginning, I will be using Alteryx as a tool to upload client data into an internal piece of software.

The software requires a particular format of column headings in a particular order.  I would like to configure something where I can define the columns within Alteryx, with the ability to map clients data at the end of the process, thus producing output in the correct format and headings.

I know this is possible as we had this in my previous role, unfortunately I do not have access to this any longer so I would like to build my own.

I don't know if this function is readily available in Alteryx or whether this would need to be custom built?

I don't want to do this on every piece of data as this is an infinity variety of formats, which is why I would like to be able to setup my template format and then have the ability to define which field from the source e.g. [VAT Amount] should be mapped to the required field in the software output.

I hope this makes sense, any help would be much appreciated.

[jacob_kahn · Accepted solution]

Hey @MT1985 

I once drafted an article on the topic. 

Please let me know if this helps - specifically, it instructs you how to build an analytical application for multiple data formats to be ran throughout the workflow. Once the user defines the fields, the workflow is built with those specific field names.

Like and accept this reply if it helps, please 🙂 

Thanks,

J

Building a Drop Down Select Tool in Analytical Applications for Dynamic Data Input

@the_jake_tool

Introduction

Often when building an analytical application, I have to consider that my users

will be uploading information that includes different header (i.e. field names). Some of the information in their data may be applicable to my tools, and some information may not be.

Therefore, I needed a way for the users to specify to the workflow which fields represented necessary information, and for the application to dynamically consider other “unnecessary” data fields.

Input

When I start building such an analytical application, I make sure that my input and workflow are structured as follows:

1. My headers are in the first row of data
2. In my input, I specify that workflow should read in a specific column range - Usually A-Z
3. If you are using a File Browse interface tool, you should use the configuration imaged below in your Action tool connected to your input tool. This confirms that when the user uploads their data, they are uploading all of their columns, even columns that have zero records in them which may be ignored for that reason by the input data tool. This is important.

Image: Input Configuration ($A:Z, First Row Contains Data)

Image: Configuration for Action tool when using File Browse interface tool

Headers

1. I use a Select Records tool to isolate that first row of information (my field headers)
2. I use another Select Records tool to remove that first row of information (my header fields)
3. I transpose the data coming out of the Select Records Tool that has my field headers in it, and rename the fields to “Original Header Names” and “Given Header Names”

Image: Select records, transpose headers, rename fields

Select

1. In order to select a field that is required in my workflow, I first decide what to name that field. In this example, I named the field “sales_information”
2. I use a drop down tool and “Manually Configure” the list of options. I configure the list of options as such: Column A: F1, Column B: F2, Column C:F3, etc.
3. In that drop down tool, the user can select which field  (i.e. column) represents “sales_information”. Make sure to configure the Action tool connected to your Select tool as is done in the image below.

Image: sales_information configuration

Image: Drow Down tool Configuration

Image: Action configuration for select tool

Dynamic Rename

At the end of the manipulation and work, I use a Dynamic Rename tool to replace the given header names (i.e. F1, F2, F3, etc.) their original names found in the data input. These are the fields that are not needed in the actual analysis within the workflow. The Dynamic Rename tool uses the transposed table of header names we created earlier to replace the given header names with the “Original Header Names”. 

Image: Dynamic rename configuration

Image: Results

Image: Analytical application

Please feel free to reach out on the Alteryx Community, LinkedIn or Instagram if you have any questions. All my contact information is included on my profile.

Thanks and be safe,

J