Hitachi Vantara Pentaho Community Wiki
Skip to end of metadata
Go to start of metadata

This method takes a column of data, and turns it into multiple columns based on the values within the column. The measure column specified is then distributed among the newly created columns.  Sparse data is handled by populating missing cells with nulls. This version of the method also takes two additional parameters - the column to sort the new columns by, and a formatter for that column.

Example

Starting Resultset

Month

Vender

Rank

Counts

Jan

A-A-A

2

92

Jan

Acme

3

200

Jan

Ajax

4

163

Feb

Acme

3

27

Feb

Ajax

4

102

Mar

Donn

1

427

Mar

A-A-A

2

301

Mar

Acme

3

82

With the following parmeters

<pivot_column>1</pivot_column>
<measures_column>3</measures_column>
<sort_by_column>2</sort_by_column>
<sort_format_type>Decimal</sort_format_type>
<sort_format_string>###,###.##</sort_format_string>

Would Become

Month

Donn

A-A-A

Acme

Ajax

Jan

null

92

200

163

Feb

null

null

27

102

Mar

427

301

82

null

Component Name:  ResultSetCrosstabComponent

Component Definitions: None 

Resources: None

Inputs:

result_set -- (Required) The input data to transform (must be a IPentahoResultSet).

pivot_column -- (Required) Integer value of the column ordinal on which to perform the pivot.

measures_column -- (Required) Integer value of the ordinal of the desired measures column to distribute to the new columns created

format_type -- (Optional) Valid values of "decimal" and "date".  Indicates that the "format_string" is of type decimal or date.  Defaults to no formatting

format_string -- (Required if format_type is defined) A string value that contains a valid java.text.DecimalFormat string or a valid java.text.SimpleDateFormat string for formatting the measure output. 

ordered_maps -- (Optional) Boolean (true/false) value that if true, will sort the new column names alphabetically.  If false, the colums will be created in the order of appearance in the rows.

sort_by_col -- (Optional) Integer value of the column to use to sort the newly created columns by.  Default to no sorting.

sort_format_type -- (Optional) Valid values of "decimal" and "date".  Indicates that the "format_string" is of type decimal or date.  This formatter is used to transform the sort column to a string.

sort_format_string -- (Required if sort_format_type is defined) A string value that contains a valid java.text.DecimalFormat string or a valid java.text.SimpleDateFormat string for transforming the sort column to a string.

unique_row_identifier_column -- (Optional) A Integer value that is a ordinal value of the column that will contain unique rows.

non_ordered -- (Optional) Boolean (true/false) value that if true will cause the transformation to work as if unique_row_identifier_column is not defined.  Defaults false. 

Outputs:

Arbitrary output name that will be mapped to the resulting IPentahoResultset

  • No labels

1 Comment

  1. This component is very handy to use with chartcomponent. Here's a working example:

    <action-definition>
    <component-name>ResultSetCrosstabComponent</component-name>
    <action-type>CrossTab it</action-type>
    <action-inputs>
    <result_set type="result-set" mapping="query_result"/>
    </action-inputs>
    <action-outputs>
    <query-result2 type="result-set" mapping="query_result2"/>
    </action-outputs>
    <component-definition>
    <pivot_column>2</pivot_column>
    <measures_column>3</measures_column>
    <sort_by_column>1</sort_by_column>
    </component-definition>
    </action-definition>