[SOLVED] Question to "sum" aggregation when Pivoting

MacPhotoBiker

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
C1	A1	100	10
C1	A1	150	15
C1	A2	200	20

(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:

Customer	Amount_A1	AmountA2	Quantity_A1	Quantity_A2
C1	150	200	15	20

However, I would have expected this:

Customer	Amount_A1	AmountA2	Quantity_A1	Quantity_A2
C1	250	200	25	20

Could somebody help me to understand what I'm doing wrong?

Nils_Woehler

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

MacPhotoBiker

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!