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

pallav

Hello All i have date something like this .


What is want to achieve is divide each attribute with with row value say suppose in "OOP_BUCKET" we have 0-0 for both "Metric" Total claim and Total Reversal . I want to create new row 0-0 % where total count will be "27 / 244281"  and same for 102-204 % rows say suppose APR-18 will be 147 / 5985.3. Attaching sample below . 


The lower rows will be product of division of total reversal / Total claims . 

Thanks in Advance looking forward to get more robust process for this task . 

hbajpai

@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>

hbajpai

Hey @pallav ,

There are many ways to approach it. From what I gather you could create a datasets with OOP bucket as id's and melt your other columns wrt to claims and reversals (you can use RM pivot operation for this, you might need some pre processing also). You would end up with attributes like Apr-18-Claims and Apr-18-Reversals and then you can just use generate attributes to calculate reversal percentage attributes. Another way could be you have separate tables for claims and reversals and then in a loop filter on ids (OOP-Bucket) and calculate reversal percentage inside the loop using generate attributes operator. Hope this helps, let me know if you have any questions.  

pallav

@hbajpai - second approach looks fine to me but how to calculate that reversal percentage . 
do we have some working example like this .?

hbajpai

@pallav
Can you share your data as a file ?  I can create and send out the process to you.

pallav

@hbajpai - sorry for delay response . please find the file as attachment.  We have to calculate percentage by dividing "total claim/ total reversal" for each bucket and for each month . 

pallav

@hbajpai - Months given here like "201801" or 201801 can be dynamic ,

pallav

Hi- Can anyone assist me here . 

hbajpai

@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>