Compare value with 5 others and pick the closest, but smaller value

kp903847kp903847 Member Posts: 5 Contributor I
edited December 2018 in Help

Hello altogether,

i am facing a logistic problem and hope that someone can help:

Lets say i have got a column which contains the delivered quantity and in 5 other columns i have the different package sizes.

E.g.

The Quantity is 21 pieces and the package units for this material are: 1 unit, 10 per carton, 25 per box and 100 per palette and so on.

It is obvious, that the delivery has been carried out with 2,1 boxes (i know that this is not quite correct but this is the aprox best solution).

What i want is that rapidminer shows the next smaller value in an extra column.

 

Ive attached an image for better understanding

 

Thanks for your help! ;-)

Answers

  • kp903847kp903847 Member Posts: 5 Contributor I

    Do you maybe know something @mschmitz? I saw that you replied to @eldenoso, which's topic seemingly was comparable to mine.

    Thanks in advance :)

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hi,

     

    nothing quick.. I would write a groovy script but i am not sure if this counts as solution. Maybe @sgenzer  can help?

     

    Best,

    Martin

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    hello @kp903847 - ok I have some thoughts but let me just understand better.  If you have quantity=21, you are looking at 2,1 cartons, not boxes - correct?  And you want RapidMiner to tell you the number of each size of shipping container you will need so that you maximize?  Something like this:

     

    quantity        palette      box     carton     single units

    21                  0                0           1               1

    40                  0                1            1               5

     

    Is that correct?  This might be what your headers are but I cannot decipher them.

     

    Scott

     

  • kp903847kp903847 Member Posts: 5 Contributor I

    Good Morning altogether,

     

    this kind of solution was something i was looking for. i solved it in excel with about 100.000 rows but now iam facing 1,7 million rows which isnt handable in excel. maybe the function i used in excel is helpful. 

     

     

    thanks alot!

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hi kp903847,

     

    i've taken the time and build a quick groovy script. it takes four attributes and adds a new attribute called min_att which tells you which attribute was the minimum of these. The attributes are at the moment hard coded. you can just change the lines with att.add( " ..") to your attribute names (likely deltas) to get what you want.

     

    Is that what you need? I can easily turn this into an operator with a nicer interface (e.g. attribute selection and min,max, value mode).

     

    Best,

    Martin

     

     

    <?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="7.6.001" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="generate_data" compatibility="7.6.001" expanded="true" height="68" name="Generate Data" width="90" x="45" y="34"/>
    <operator activated="true" class="execute_script" compatibility="7.6.001" expanded="true" height="82" name="Execute Script" width="90" x="313" y="34">
    <parameter key="script" value="/* &#10; * You can use both Java and Groovy syntax in this script.&#10; * &#10; * Note that you have access to the following two predefined variables:&#10; * 1) input (an array of all input data)&#10; * 2) operator (the operator instance which is running this script)&#10; */&#10;&#10;// Take first input data and treat it as generic IOObject&#10;// Alternatively, you could treat it as an ExampleSet if it is one:&#10;// ExampleSet inputData = input[0];&#10;import com.rapidminer.example.Attribute;&#10;import com.rapidminer.example.Example;&#10;import com.rapidminer.example.ExampleSet;&#10;import com.rapidminer.example.table.AttributeFactory;&#10;import com.rapidminer.operator.Operator;&#10;import com.rapidminer.operator.OperatorDescription;&#10;import com.rapidminer.tools.Ontology;&#10;&#10;import java.util.LinkedList;&#10;import java.util.List;&#10;&#10;ExampleSet exampleSet = input[0];&#10;&#10; List&lt;String&gt; att = new LinkedList&lt;String&gt;();&#10; att.add(&quot;att1&quot;);&#10; att.add(&quot;att2&quot;);&#10; att.add(&quot;att3&quot;);&#10; att.add(&quot;att4&quot;);&#10; Attribute minAtt = AttributeFactory.createAttribute(&quot;min_attribute&quot;, Ontology.POLYNOMINAL);&#10; exampleSet.getExampleTable().addAttribute(minAtt);&#10; exampleSet.getAttributes().addRegular(minAtt);&#10; for(Example e : exampleSet){&#10; double value = 0;&#10; String bestatt = null;&#10; double bestvalue = Double.NaN;&#10; for(String a : att){&#10; value = e.getValue(e.getAttributes().get(a));&#10; if(value &lt; bestvalue || Double.isNaN(bestvalue)){&#10; bestatt = a;&#10; bestvalue = value;&#10; }&#10; e.setValue(minAtt,minAtt.getMapping().mapString(bestatt));&#10; }&#10;&#10; }&#10;// You can add any code here&#10;&#10;&#10;// This line returns the first input as the first output&#10;return exampleSet;"/>
    </operator>
    <connect from_op="Generate Data" from_port="output" to_op="Execute Script" to_port="input 1"/>
    <connect from_op="Execute Script" from_port="output 1" 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>

     

    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • kp903847kp903847 Member Posts: 5 Contributor I

    greetings martin!

     

    thanks alot for taking time for my issue. the algorithm works fine with positive values, but identifies the wrong one when there are negative values.

     

    e.g.

     

     

  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    Hi,

     

    attached is the groovy with abs() around the values.

     

    Best,

    Martin

    <?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="7.6.001" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="generate_data" compatibility="7.6.001" expanded="true" height="68" name="Generate Data" width="90" x="45" y="34"/>
    <operator activated="true" class="execute_script" compatibility="7.6.001" expanded="true" height="82" name="Execute Script" width="90" x="313" y="34">
    <parameter key="script" value="/* &#10; * You can use both Java and Groovy syntax in this script.&#10; * &#10; * Note that you have access to the following two predefined variables:&#10; * 1) input (an array of all input data)&#10; * 2) operator (the operator instance which is running this script)&#10; */&#10;&#10;// Take first input data and treat it as generic IOObject&#10;// Alternatively, you could treat it as an ExampleSet if it is one:&#10;// ExampleSet inputData = input[0];&#10;import com.rapidminer.example.Attribute;&#10;import com.rapidminer.example.Example;&#10;import com.rapidminer.example.ExampleSet;&#10;import com.rapidminer.example.table.AttributeFactory;&#10;import com.rapidminer.operator.Operator;&#10;import com.rapidminer.operator.OperatorDescription;&#10;import com.rapidminer.tools.Ontology;&#10;&#10;import java.util.LinkedList;&#10;import java.util.List;&#10;&#10;ExampleSet exampleSet = input[0];&#10;&#10; List&lt;String&gt; att = new LinkedList&lt;String&gt;();&#10; att.add(&quot;att1&quot;);&#10; att.add(&quot;att2&quot;);&#10; att.add(&quot;att3&quot;);&#10; att.add(&quot;att4&quot;);&#10; Attribute minAtt = AttributeFactory.createAttribute(&quot;min_attribute&quot;, Ontology.POLYNOMINAL);&#10; exampleSet.getExampleTable().addAttribute(minAtt);&#10; exampleSet.getAttributes().addRegular(minAtt);&#10; for(Example e : exampleSet){&#10; double value = 0;&#10; String bestatt = null;&#10; double bestvalue = Double.NaN;&#10; for(String a : att){&#10; value = e.getValue(e.getAttributes().get(a));&#10; if(Math.abs(value) &lt; Math.abs(bestvalue) || Double.isNaN(bestvalue)){&#10; bestatt = a;&#10; bestvalue = value;&#10; }&#10; e.setValue(minAtt,minAtt.getMapping().mapString(bestatt));&#10; }&#10;&#10; }&#10;// You can add any code here&#10;&#10;&#10;// This line returns the first input as the first output&#10;return exampleSet;"/>
    </operator>
    <connect from_op="Generate Data" from_port="output" to_op="Execute Script" to_port="input 1"/>
    <connect from_op="Execute Script" from_port="output 1" 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>
    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
  • MartinLiebigMartinLiebig Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, University Professor Posts: 3,503 RM Data Scientist

    For future reference,

     

    we solved this via PM. I missunderstood the question a bit. The solution is attached.

     

    ~Martin

    <?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
    <context>
    <input/>
    <output/>
    <macros/>
    </context>
    <operator activated="true" class="process" compatibility="7.6.001" expanded="true" name="Process">
    <process expanded="true">
    <operator activated="true" class="subprocess" compatibility="7.6.001" expanded="true" height="82" name="Subprocess" width="90" x="45" y="34">
    <process expanded="true">
    <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification" width="90" x="45" y="34">
    <list key="attribute_values">
    <parameter key="TTE" value="0/0"/>
    <parameter key="KAR" value="0/0"/>
    <parameter key="PAL" value="1800"/>
    <parameter key="AKR" value="10"/>
    <parameter key="Picked" value="10"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (2)" width="90" x="45" y="136">
    <list key="attribute_values">
    <parameter key="TTE" value="0/0"/>
    <parameter key="KAR" value="0/0"/>
    <parameter key="PAL" value="900"/>
    <parameter key="AKR" value="5"/>
    <parameter key="Picked" value="14"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (3)" width="90" x="45" y="238">
    <list key="attribute_values">
    <parameter key="TTE" value="0/0"/>
    <parameter key="KAR" value="1"/>
    <parameter key="PAL" value="0/0"/>
    <parameter key="AKR" value="0/0"/>
    <parameter key="Picked" value="1"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="generate_data_user_specification" compatibility="7.6.001" expanded="true" height="68" name="Generate Data by User Specification (4)" width="90" x="45" y="340">
    <list key="attribute_values">
    <parameter key="TTE" value="0/0"/>
    <parameter key="KAR" value="10"/>
    <parameter key="PAL" value="320"/>
    <parameter key="AKR" value="0/0"/>
    <parameter key="Picked" value="3"/>
    </list>
    <list key="set_additional_roles"/>
    </operator>
    <operator activated="true" class="append" compatibility="7.6.001" expanded="true" height="145" name="Append" width="90" x="380" y="85"/>
    <connect from_op="Generate Data by User Specification" from_port="output" to_op="Append" to_port="example set 1"/>
    <connect from_op="Generate Data by User Specification (2)" from_port="output" to_op="Append" to_port="example set 2"/>
    <connect from_op="Generate Data by User Specification (3)" from_port="output" to_op="Append" to_port="example set 3"/>
    <connect from_op="Generate Data by User Specification (4)" from_port="output" to_op="Append" to_port="example set 4"/>
    <connect from_op="Append" from_port="merged set" to_port="out 1"/>
    <portSpacing port="source_in 1" spacing="0"/>
    <portSpacing port="sink_out 1" spacing="0"/>
    <portSpacing port="sink_out 2" spacing="0"/>
    </process>
    </operator>
    <operator activated="true" class="generate_attributes" compatibility="7.6.001" expanded="true" height="82" name="Generate Attributes (2)" width="90" x="246" y="34">
    <list key="function_descriptions">
    <parameter key="Closest" value="INFINITY"/>
    <parameter key="Delta" value="INFINITY"/>
    <parameter key="OldDelta" value="INFINITY"/>
    </list>
    <description align="center" color="transparent" colored="false" width="126">Initialize Loop Variables</description>
    </operator>
    <operator activated="true" class="concurrency:loop_attributes" compatibility="7.6.001" expanded="true" height="82" name="Loop Attributes" width="90" x="514" y="34">
    <parameter key="attribute_filter_type" value="subset"/>
    <parameter key="attributes" value="TTE|PAL|KAR|AKR"/>
    <parameter key="reuse_results" value="true"/>
    <process expanded="true">
    <operator activated="true" class="generate_attributes" compatibility="7.6.001" expanded="true" height="82" name="Generate Attributes" width="90" x="179" y="34">
    <list key="function_descriptions">
    <parameter key="Delta" value="abs(eval(%{loop_attribute}) - Picked)"/>
    <parameter key="Closest" value="if(Delta &lt; OldDelta,eval(%{loop_attribute}),Closest)"/>
    <parameter key="OldDelta" value="if(Delta &lt; OldDelta,Delta,OldDelta)"/>
    </list>
    </operator>
    <connect from_port="input 1" to_op="Generate Attributes" to_port="example set input"/>
    <connect from_op="Generate Attributes" 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"/>
    <description align="center" color="yellow" colored="false" height="71" resized="true" width="558" x="57" y="194">1. Calculate delta between Picked Qty and Looped Option&lt;br&gt;2.If delta &amp;lt; oldDelta - &amp;gt; update Smaller &lt;br/&gt;if delta&amp;lt; oldDelta, oldDelta=delta</description>
    </process>
    </operator>
    <operator activated="true" class="select_attributes" compatibility="7.6.001" expanded="true" height="82" name="Select Attributes" width="90" x="782" y="34">
    <parameter key="attribute_filter_type" value="subset"/>
    <parameter key="attributes" value="OldDelta|Delta"/>
    <parameter key="invert_selection" value="true"/>
    </operator>
    <connect from_op="Subprocess" from_port="out 1" to_op="Generate Attributes (2)" to_port="example set input"/>
    <connect from_op="Generate Attributes (2)" from_port="example set output" to_op="Loop Attributes" to_port="input 1"/>
    <connect from_op="Loop Attributes" from_port="output 1" to_op="Select Attributes" to_port="example set input"/>
    <connect from_op="Select Attributes" 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>
    - Sr. Director Data Solutions, Altair RapidMiner -
    Dortmund, Germany
Sign In or Register to comment.