[SOLVED] Question to "sum" aggregation when Pivoting

MacPhotoBikerMacPhotoBiker Member Posts: 60 Contributor II
Hi,
I'm trying to create a rather simple Pivot Table using the "Pivoting" operator.

Let's say this is the raw data:
Customer Article Amount Quantity
C1A1 10010
C1A1 15015
C1A2 20020
(Note: I initially posted the wrong raw numbers in the third row, but it's corrected now.)


The Pivot operator is configured as follows:

Group Attribute:        Customer
Index Attribute:         Article
Weight Aggrecation: sum

When I run the process, I get this result:
CustomerAmount_A1AmountA2Quantity_A1Quantity_A2
C11502001520
However, I would have expected this:
CustomerAmount_A1AmountA2Quantity_A1Quantity_A2
C12502002520
Could somebody help me to understand what I'm doing wrong?

Answers

  • Nils_WoehlerNils_Woehler Member Posts: 463 Maven
    Hi,

    the pivot operator does not aggregate data values. Just weights are aggregated thus the name attribute name weighte aggregation.
    What you want to use is the Aggregate operator:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="5.3.009">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="5.3.009" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="true" class="read_csv" compatibility="5.3.009" expanded="true" height="60" name="Read CSV" width="90" x="112" y="255">
            <parameter key="csv_file" value="C:\Users\nwoehler\Desktop\test.csv"/>
            <parameter key="column_separators" value=","/>
            <parameter key="first_row_as_names" value="false"/>
            <list key="annotations">
              <parameter key="0" value="Name"/>
            </list>
            <parameter key="encoding" value="windows-1252"/>
            <list key="data_set_meta_data_information">
              <parameter key="0" value="customer.true.binominal.attribute"/>
              <parameter key="1" value="article.true.binominal.attribute"/>
              <parameter key="2" value="amount.true.integer.attribute"/>
              <parameter key="3" value="quantity.true.integer.attribute"/>
            </list>
          </operator>
          <operator activated="true" class="aggregate" compatibility="5.3.009" expanded="true" height="76" name="Aggregate" width="90" x="246" y="255">
            <list key="aggregation_attributes">
              <parameter key="quantity" value="sum"/>
              <parameter key="amount" value="sum"/>
            </list>
            <parameter key="group_by_attributes" value="article|customer|"/>
          </operator>
          <connect from_op="Read CSV" from_port="output" to_op="Aggregate" to_port="example set input"/>
          <connect from_op="Aggregate" from_port="example set output" to_port="result 1"/>
          <portSpacing port="source_input 1" spacing="0"/>
          <portSpacing port="sink_result 1" spacing="0"/>
          <portSpacing port="sink_result 2" spacing="0"/>
        </process>
      </operator>
    </process>
    Best,
    Nils
  • MacPhotoBikerMacPhotoBiker Member Posts: 60 Contributor II
    Hi Nils,

    thank you very much for clarifying. You are right, the operator clearly states "weight aggregation".

    All works well now, I simply added the aggregation operator before pivoting, and I'm getting the correct results.

    Thanks for helping out!
Sign In or Register to comment.