Dividing one row with another and create new row in same attribute.

Best Answer

• Options

  hbajpai Member Posts: 102 Unicorn

  August 2020 Solution Accepted

  @pallav
  
  I have created a process that does the calculations as you desired. It can be a simpler if we could clean the data at source a little bit, but either way, I used loop values over bucket to generate the total percent. If you want to replicate the results make sure you import B3:M9 and header as row 3. 
  
  
  
  

  <?xml version="1.0" encoding="UTF-8"?><process version="9.7.002">
    <context>
      <input/>
      <output/>
      <macros/>
    </context>
    <operator activated="true" class="process" compatibility="9.7.002" expanded="true" name="Process">
      <parameter key="logverbosity" value="init"/>
      <parameter key="random_seed" value="2001"/>
      <parameter key="send_mail" value="never"/>
      <parameter key="notification_email" value=""/>
      <parameter key="process_duration_for_mail" value="30"/>
      <parameter key="encoding" value="SYSTEM"/>
      <process expanded="true">
        <operator activated="true" class="read_excel" compatibility="9.7.002" expanded="true" height="68" name="Read Excel" width="90" x="112" y="34">
          <parameter key="excel_file" value="C:\Users\harsh\Downloads\Data_file.xlsx"/>
          <parameter key="sheet_selection" value="sheet number"/>
          <parameter key="sheet_number" value="1"/>
          <parameter key="imported_cell_range" value="B3:M9"/>
          <parameter key="encoding" value="SYSTEM"/>
          <parameter key="first_row_as_names" value="true"/>
          <list key="annotations"/>
          <parameter key="date_format" value=""/>
          <parameter key="time_zone" value="SYSTEM"/>
          <parameter key="locale" value="English (United States)"/>
          <parameter key="read_all_values_as_polynominal" value="false"/>
          <list key="data_set_meta_data_information">
            <parameter key="0" value="Metrix.true.polynominal.attribute"/>
            <parameter key="1" value="Bucket.true.polynominal.attribute"/>
            <parameter key="2" value="201801\.0.true.real.attribute"/>
            <parameter key="3" value="201802\.0.true.real.attribute"/>
            <parameter key="4" value="201803\.0.true.real.attribute"/>
            <parameter key="5" value="201804\.0.true.real.attribute"/>
            <parameter key="6" value="201805\.0.true.real.attribute"/>
            <parameter key="7" value="201806\.0.true.real.attribute"/>
            <parameter key="8" value="201807\.0.true.real.attribute"/>
            <parameter key="9" value="201808\.0.true.real.attribute"/>
            <parameter key="10" value="201809\.0.true.real.attribute"/>
            <parameter key="11" value="201810\.0.true.real.attribute"/>
          </list>
          <parameter key="read_not_matching_values_as_missings" value="false"/>
          <parameter key="datamanagement" value="double_array"/>
          <parameter key="data_management" value="auto"/>
        </operator>
        <operator activated="true" class="concurrency:loop_values" compatibility="9.7.002" expanded="true" height="82" name="Loop Values" width="90" x="380" y="34">
          <parameter key="attribute" value="Bucket"/>
          <parameter key="iteration_macro" value="loop_value"/>
          <parameter key="reuse_results" value="false"/>
          <parameter key="enable_parallel_execution" value="true"/>
          <process expanded="true">
            <operator activated="true" class="filter_examples" compatibility="9.7.002" expanded="true" height="103" name="Filter Examples" width="90" x="45" y="34">
              <parameter key="parameter_expression" value=""/>
              <parameter key="condition_class" value="custom_filters"/>
              <parameter key="invert_filter" value="false"/>
              <list key="filters_list">
                <parameter key="filters_entry_key" value="Bucket.equals.%{loop_value}"/>
              </list>
              <parameter key="filters_logic_and" value="true"/>
              <parameter key="filters_check_metadata" value="true"/>
            </operator>
            <operator activated="true" class="transpose" compatibility="9.7.002" expanded="true" height="82" name="Transpose (2)" width="90" x="179" y="34"/>
            <operator activated="true" class="rename_by_example_values" compatibility="9.7.002" expanded="true" height="82" name="Rename by Example Values (2)" width="90" x="313" y="34">
              <parameter key="row_number" value="1"/>
            </operator>
            <operator activated="true" class="filter_examples" compatibility="9.7.002" expanded="true" height="103" name="Filter Examples (2)" width="90" x="447" y="34">
              <parameter key="parameter_expression" value=""/>
              <parameter key="condition_class" value="custom_filters"/>
              <parameter key="invert_filter" value="true"/>
              <list key="filters_list">
                <parameter key="filters_entry_key" value="Metrix.equals.Bucket"/>
              </list>
              <parameter key="filters_logic_and" value="true"/>
              <parameter key="filters_check_metadata" value="true"/>
            </operator>
            <operator activated="true" class="guess_types" compatibility="9.7.002" expanded="true" height="82" name="Guess Types" width="90" x="581" y="34">
              <parameter key="attribute_filter_type" value="all"/>
              <parameter key="attribute" value=""/>
              <parameter key="attributes" value=""/>
              <parameter key="use_except_expression" value="false"/>
              <parameter key="value_type" value="attribute_value"/>
              <parameter key="use_value_type_exception" value="false"/>
              <parameter key="except_value_type" value="time"/>
              <parameter key="block_type" value="attribute_block"/>
              <parameter key="use_block_type_exception" value="false"/>
              <parameter key="except_block_type" value="value_matrix_row_start"/>
              <parameter key="invert_selection" value="false"/>
              <parameter key="include_special_attributes" value="false"/>
              <parameter key="decimal_point_character" value="."/>
            </operator>
            <operator activated="true" class="generate_attributes" compatibility="9.7.002" expanded="true" height="82" name="Generate Attributes" width="90" x="715" y="34">
              <list key="function_descriptions">
                <parameter key="Total Percent" value="[Total Claims] / [Total Reversals]"/>
              </list>
              <parameter key="keep_all" value="true"/>
            </operator>
            <operator activated="true" class="transpose" compatibility="9.7.002" expanded="true" height="82" name="Transpose (3)" width="90" x="849" y="34"/>
            <operator activated="true" class="rename" compatibility="9.7.002" expanded="true" height="82" name="Rename" width="90" x="983" y="34">
              <parameter key="old_name" value="id"/>
              <parameter key="new_name" value="Metrix"/>
              <list key="rename_additional_attributes"/>
            </operator>
            <operator activated="true" class="generate_attributes" compatibility="9.7.002" expanded="true" height="82" name="Generate Attributes (2)" width="90" x="1117" y="34">
              <list key="function_descriptions">
                <parameter key="Bucket" value="%{loop_value}"/>
              </list>
              <parameter key="keep_all" value="true"/>
            </operator>
            <connect from_port="input 1" to_op="Filter Examples" to_port="example set input"/>
            <connect from_op="Filter Examples" from_port="example set output" to_op="Transpose (2)" to_port="example set input"/>
            <connect from_op="Transpose (2)" from_port="example set output" to_op="Rename by Example Values (2)" to_port="example set input"/>
            <connect from_op="Rename by Example Values (2)" from_port="example set output" to_op="Filter Examples (2)" to_port="example set input"/>
            <connect from_op="Filter Examples (2)" from_port="example set output" to_op="Guess Types" to_port="example set input"/>
            <connect from_op="Guess Types" from_port="example set output" to_op="Generate Attributes" to_port="example set input"/>
            <connect from_op="Generate Attributes" from_port="example set output" to_op="Transpose (3)" to_port="example set input"/>
            <connect from_op="Transpose (3)" from_port="example set output" to_op="Rename" to_port="example set input"/>
            <connect from_op="Rename" from_port="example set output" to_op="Generate Attributes (2)" to_port="example set input"/>
            <connect from_op="Generate Attributes (2)" from_port="example set output" to_port="output 1"/>
            <portSpacing port="source_input 1" spacing="0"/>
            <portSpacing port="source_input 2" spacing="0"/>
            <portSpacing port="sink_output 1" spacing="0"/>
            <portSpacing port="sink_output 2" spacing="0"/>
          </process>
        </operator>
        <operator activated="true" class="append" compatibility="9.7.002" expanded="true" height="82" name="Append" width="90" x="514" y="34">
          <parameter key="datamanagement" value="double_array"/>
          <parameter key="data_management" value="auto"/>
          <parameter key="merge_type" value="all"/>
        </operator>
        <connect from_op="Read Excel" from_port="output" to_op="Loop Values" to_port="input 1"/>
        <connect from_op="Loop Values" from_port="output 1" to_op="Append" to_port="example set 1"/>
        <connect from_op="Append" from_port="merged set" 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,
  Harshit

  5