Greetings,

I am trying to convert some data from an Excel spreadsheet into a format
suitable for a data warehouse. Of the many steps required, one concerns a
pivot of the Excel data. So I am trying to use the Pivot Transform, but
without getting the results I am expecting.

There are five input columns, three of which identify the set (Date, Symbol
and Strategy). The fourth column holds the Type and this is the pivot
column. The fifth column holds the Amount. My intention is to get one row
for each distinct set of Date, Symbol and Strategy plus extra columns for the
three possible values in the Type column.

I have set the input column PivotUsage property accordingly (1 for the first
three columns, 2 for the Type column and 3 for the Amount). On the Output
side, I have set the SourceColumn properties to match the lineage values of
the input columns (with the new pivot columns pointing to the lineage for the
Type input column). I have also set the PivotKeyValue property for the pivot
columns.

Trouble is that while the output columns show amounts in the correct pivot
columns, the transform has not reduced the number of rows. For my 4 test
symbols, with 51 dates and 3 types, I expect 612 rows going into the Pivot
Transform and 204 coming out (612/3). But I am getting 612 rows instead.

I can see from the DataViewer that rather than amalgamating the 3 rows into
one, the transformation is correctly allocating the amount to the correct
pivot column, but putting NULL values into the other two possible pivot
columns. It is as if the transformation is doing two thirds of the work, but
failing to 'squash' the result rows so that there is only one row for the
Date, Symbol and Strategy that define the set.

I wonder if I am expecting too much from this transform? Any ideas or
suggestions gratefully received.

With thanks and regards

Sebastian Crewe

I had a similar problem and have now solved it. You need to have a Sort
transformation before the pivot. The Sort transformation should sort on the
column that has your row headers. Once you precede the Sort transformation
before the Pivot, you will see that it automatically sets the IsSorted
property of the Input Columns to True.

Charles Kangai, MCT, MCDBA



"Sebastian Crewe" wrote:

Many thanks. It is a while ago now, but I think that is how I did solve the
problem, hence giving a Yes to your resposne in the hope that it will help
others.

"Charles Kangai" wrote: