0088 Dynamic Pivot

Original forum post

Data example

Desired output

Answer

Yes, but not with the standard Pivot transformation when the number of dates is unknown. Build the header dynamically, build the matching quantity lists, then combine them. Dynamic pivots are useful because source systems rarely ask politely before changing the columns.

How it works

1. Create a sorted list of dates delimited by comma. This becomes the header.
2. For every supplier material, create a comma-delimited list of quantities sorted by date, filling gaps with zeros.
3. Combine the header and sales rows.

Additional writers are included to make the example easier to understand. They can be deleted except for the final result writer.

Creating the header

The SHIP_DATE field is reformatted, deduplicated, and sorted. Then the <Header> variable is populated using the Accumulate Strings transformation function.

Transformation

Transformation result

Creating QTY lists

Data is joined with a list of dates.

Data is deduplicated and the QTY list is created.

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

Calculating Data Field

When the row number equals 1, use the <Header> variable as the value. Otherwise, use SUPPLIER_MAT plus the QTY list.

Transformation result

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
• Double-click the Sorter object and amend the source file path
• Press the green arrow to run the transformation.