Transpose Rows to Columns using Pentaho Data Integration

Sample Data

In the data given below, it is expected to pivot column Week from rows to columns. Following data is the number of vehicles sold by particular user in a week for two different user accounts.

• Account;Week;Sale
• 334234;Week_1;23;
• 334234;Week_2;72
• 334234;Week_3;14
• 116675;Week_1;33
• 116675;Week_3;78

Expected output

Based on above data it is expected that the data is distributed like sale for each week for respective Account.

Account;Week_1;Week_2;Week_3
334234;23;72;14
116675;33;;78

Pentaho Data integration provides the ability to transpose your required data with the help of a transformation Component Row de-normalizer.

Sort Rows is important to transpose properly

So based on my sample data and required o/p weeks sales need to be columns hence the key field is week

And we want to group by the account and you specify what columns you want based on the Key Field Week

Run:

Thanks
Sohail Izebhijie
BI Developer
Helical IT Solutions Pvt Ltd

Best Open Source Business Intelligence Software Helical Insight Here

A Business Intelligence Framework

Free
Download

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download