Pivot Stage Example
Pivot enterprise stage is one of a processing stage which pivots data vertically and horizontally depending upon the user requirements. There are two types of Pivot:
1. Horizontal Pivot:
Horizontal Pivot operation maps input columns to the multiple rows, resulting in more rows than the input data. Basically we can map several sets of input columns to several output rows.
2. Vertical Pivot:
Vertical pivot operation maps a set of rows in the input data to single or multiple output columns. The array size determines the number of rows in the output data. The output data of the vertical pivot action typically has more columns, but fewer rows than the input data.
Horizontal Pivot operation:
Here, we are having Car.txt as our input where there are 5 columns as an input and we would like to pivot the data on CarSegment column so that we can have only multiple rows for the same which means only 2 columns i.e. CarSegment and Company as an output.
Design the job as shown below:
Open the Properties window by double click or Right clickàProperties from the drop down menu. Under StageàProperties tab select Pivot type as Horizontal, similar to below snapshot.
Next, Go to Pivot Properties tab and select the Checkbox ‘Pivot Index’ which is situated down to the left corner of the window. Pivot Index will appear as shown below.
Pivot index can be used to get Pivot value for that column in target.
Click on Load button down to right corner, to load the column definitions required in output.
Next, Edit the Derivation field to Pivot on Company by double clicking on Company derivation. A Column selection dialog will popup, select the columns need to pivoted as below.
Click Ok; we will be able to see the derivation populated under derivation option as below.
Input columns will look like below under StageàInput tab of Pivot stage properties.
Under StageàOutput tab of Pivot stage properties tab, map the input columns to the output columns as per the requirements. As in output we need to pivot input columns to multiple rows i.e. Pivot (Company) hence we have pulled CarSegment and Pivot(Company) as Company as shown below.
Save, Compile and run the job. Post successful completion of the job the output will contain more no. of rows as below.