Generating Aggregate Table in specific format - Pivot

jeganathanvelujeganathanvelu Member Posts: 17 Contributor II
edited June 2019 in Help
Hi All,

I googled on this but still could not figure out how to do this using pivot function/alternative functions. Requesting your help.

I have a table as below:

Attribute 1    Attribute 2

A                    Z1
B                    Z1
C                    Z2
D                    Z2
E                    Z2
F                    Z3

I want to  generate a report like this with count between both the attributes in a matrix format.


        Z1            Z2
A    1                  0
B      1                0
C      0                1
D      0                1


Thanks in Advance,
Jegan

Answers

  • frasfras Member Posts: 93 Contributor II
    You need a quantifier column otherwise pivoting does not know which values to insert:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <process version="6.0.003">
      <context>
        <input/>
        <output/>
        <macros/>
      </context>
      <operator activated="true" class="process" compatibility="6.0.003" expanded="true" name="Process">
        <process expanded="true">
          <operator activated="true" class="read_csv" compatibility="6.0.003" expanded="true" height="60" name="Read CSV" width="90" x="45" y="210">
            <parameter key="csv_file" value="http://pastebin.com/raw.php?i=ASgMba0T"/>
            <parameter key="column_separators" value="\s+"/>
            <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="Attribute01.true.polynominal.attribute"/>
              <parameter key="1" value="Attribute02.true.polynominal.attribute"/>
            </list>
          </operator>
          <operator activated="true" breakpoints="after" class="generate_attributes" compatibility="6.0.003" expanded="true" height="76" name="Generate Attributes (2)" width="90" x="179" y="210">
            <list key="function_descriptions">
              <parameter key="quantity" value="1"/>
            </list>
          </operator>
          <operator activated="true" class="pivot" compatibility="6.0.003" expanded="true" height="76" name="Pivot" width="90" x="313" y="210">
            <parameter key="group_attribute" value="Attribute01"/>
            <parameter key="index_attribute" value="Attribute02"/>
            <parameter key="consider_weights" value="false"/>
            <parameter key="weight_aggregation" value="count"/>
            <parameter key="skip_constant_attributes" value="false"/>
          </operator>
          <operator activated="true" class="rename_by_replacing" compatibility="6.0.003" expanded="true" height="76" name="Rename by Replacing" width="90" x="447" y="210">
            <parameter key="replace_what" value="quantity_(.*)"/>
            <parameter key="replace_by" value="$1"/>
          </operator>
          <operator activated="true" class="replace_missing_values" compatibility="6.0.003" expanded="true" height="94" name="Replace Missing Values" width="90" x="581" y="210">
            <parameter key="default" value="value"/>
            <list key="columns"/>
            <parameter key="replenishment_value" value="0"/>
          </operator>
          <connect from_op="Read CSV" from_port="output" to_op="Generate Attributes (2)" to_port="example set input"/>
          <connect from_op="Generate Attributes (2)" from_port="example set output" to_op="Pivot" to_port="example set input"/>
          <connect from_op="Pivot" from_port="example set output" to_op="Rename by Replacing" to_port="example set input"/>
          <connect from_op="Rename by Replacing" from_port="example set output" to_op="Replace Missing Values" to_port="example set input"/>
          <connect from_op="Replace Missing Values" 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>


  • jeganathanvelujeganathanvelu Member Posts: 17 Contributor II
    Hi fras,

    Thanks a Lot Now I learnt how to generate such tables.

    . I think I phrased the question wrong. Instead of just having "1" in the pivot, I need the count of Z1,Z2 against attribute 1 in the matrices. Could you please kindly help me ??

    Thanks in Advance,
    Jeganathan Velu.
Sign In or Register to comment.