Dynamic Pivot

This example demonstrates creating a complex Dynamic Pivot.

Question from the customer:

Is it possible to make a pivot with dynamic fields?
I have a table with all the different dates.
Can I make a pivot?

[SUPPLIER MAT#] --> [DELIVERY DATE] --> [QTY]

As I understand it, otherwise I need a fixed allocation.
With all the different dates an allocation is rather not possible.

Original forum post

Data Example

Desired Output

The Problem

Since we do not know the exact number of dates it is not possible to use the standard Advanced ETL Processor Pivot transformation

The Solution

How it works

1. Create a sorted list of dates delimited by comma (Header)
2. For every supplier material create a comma-delimited list of QTYs sorted by date filling the gaps with zeros
3. Combine header and sales

In order to make it easier to understand we included additional writers
All of them can be deleted except the result

Creating the header

SHIP_DATE field is reformated, deduplicated and sorted. Then the <Header> variable is populated with the help of Accumulate Strings transformation function

Transformation

Transformation result

Creating QTY lists

 Data is joined with a list of dates

 Data is deduplicated and QTY list is created 

Data is grouped by SUPPLIER_MAT and the last value of QTY list

Calculating Data Field

When the row number is equal to 1 we use <Header>  variable as the value otherwise we use SUPPLIER_MAT+QTY List 

Transformation Result

To view the example follow the steps below

• Download and install Advanced ETL Processor [Link]
• Download and Unzip example[Link]
• Create a new transformation and open the .ats file
• Double click on the Reader object and amend the source file path
• Double click on the Writers objects and amend the source file path
• Double click on the Sorters object and amend the source file path
• Press the green arrow to run the transformation 

Please contact us if you need help with transforming the data