0087 Pivoting data Using ETL Tools
A practical Advanced ETL Processor example for building, testing, and adapting one automation workflow.
Use this example to build one working automation package, test it safely, and then adapt it to your own files, folders, or data rules. Start small. Automation is helpful; automated mistakes are just faster mistakes.
Customer question
Question from the customer
I have data that looks like this.
I want to transform it to the following pivoted format.
Answer
Sort and group the data first, then pivot it. Pivoting unsorted data is where tidy reports go to develop trust issues.
How it works
- Field Selector removes the Record ID# field.
- The data is sorted on the Brands and If fields.
- The CountFlag field is added.
- The data is grouped by Brands and If fields, and the sum of CountFlag is calculated.
- The data is pivoted.
- The result is saved into an Excel file.
Example files
To view the example follow the steps below
- Download and install Advanced ETL Processor [Link]
- Download and unzip the example [Link]
- Create a new transformation and open the .ats file
- Double-click the Reader object and amend the source file path
- Double-click the Writer objects and amend the source file path
- Press the green arrow to run the transformation.
Video tutorial
Related Advanced ETL Processor resources
For more automation examples, review the Advanced ETL Processor tutorials, read the WIKI, or download the Advanced ETL Processor Enterprise trial.