Alteryx Design Pattern: Reshaping data with duplicate field names
In this Design Pattern we are going to show you how to reshape data where there are repeating fields which have the same name. This is a common structure that you may encounter when new data is added to the right, for example when a new month is added.
Below is a screenshot of the data used in the example:
City | Date | Sales | EBITDA | Date | Sales | EBITDA | Date | Sales | EBITDA |
London | 2020-01 | 100 | 10 | 2020-02 | 120 | 12 | 2020-03 | 150 | 15 |
New York | 2020-01 | 150 | 15 | 2020-02 | 170 | 17 | 2020-03 | 200 | 20 |
Paris | 2020-01 | 200 | 20 | 2020-02 | 220 | 22 | 2020-03 | 250 | 25 |
For each new month new fields for Date, Sales and EBITDA are added to the right of the dataset. However to make this useful for analysis the data needs to be reshaped into a flat file.
Using a similar Design Pattern as shown in posts such as How to modify many field values at the same time we use the combination of a transpose and cross tab tool to reshape the data. In between the ‘transpose and cross tab bookends’, we first put a Data Cleanse tool to strip out the number suffix which is added by Alteryx when there are multiple fields with the same name. Next we add a Tile Tool to get a number sequence which helps maintain the data order when the data is cross-tabbed.
In addition in the video, to maintain the field order a bonus trick is used with selecting no records from the input data and using this as the first input into the union tool.