write the loop label models outputs to different excel files

mansour · November 2023

Dear All
I wonder how I can write the output Excel for the Loop Lable model on new Excel each time, when I use this model, whenever it tries to write an Excel, it overwrites the previous loop results. I want it to be written in a new Excel each time.
Best wishes.
Mansour

MartinLiebig · November 2023

@rjones13 ,

while your solution works perfectly, you can even make it slightly easier.

There are a few "hidden macros", which are always available. The most useful one is maybe %{a}, which always gives you the execution count of the current operator.

You can just use

/path/to/file/%{a}.xlsx

as a path in Write excel.

This is the list of other macros which are always available:

%{n} or %{operator_name} with the name of this operator
%{c} with the class of this operator
%{t} with the current system date and time
%{a} or %{execution_count} with the number of times the operator was applied
%{b} with the number of times the operator was applied plus one (a shortcut for %{p[1]})
%{p[number]} with the number of times the operator was applied plus number
%{v[OperatorName.ValueName]} with the value "ValueName" of the operator "OperatorName"

%{process_name}: will be replaced by the name of the process (without path and extension)
%{process_file}: will be replaced by the file name of the process (with extension)
%{process_path}: will be replaced by the complete absolute path of the process file
%{execution_count}: will be replaced by the number of times the current operator was applied.
%{operator_name}: will be replaced by the name of the current operator.
%{tempdir} gives you the directory of the tempdir on this machine

Best,

Martin

RolandJones · November 2023

Hi Mansour,

Just checking if you have a desired naming convention for the Excel files you're producing?

What I would do here is use macros inside the loop and modify the write Excel to have that macro as a suffix. Here's a small example that you can point to your ExampleSet.

Best,
Roland

<?xml version="1.0" encoding="UTF-8"?><process version="10.2.000">

  <context>

    <input/>

    <output/>

    <macros/>

  </context>

  <operator activated="true" class="process" compatibility="10.2.000" 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="UTF-8"/>

    <process expanded="true">

      <operator activated="true" class="retrieve" compatibility="10.2.000" expanded="true" height="68" name="Retrieve dataset" width="90" x="45" y="34">

        <parameter key="repository_entry" value="//Local Repository/Demos/Full-Iris"/>

      </operator>

      <operator activated="true" class="set_macro" compatibility="10.2.000" expanded="true" height="82" name="Set Macro" width="90" x="179" y="34">

        <parameter key="macro" value="counter"/>

        <parameter key="value" value="1"/>

      </operator>

      <operator activated="true" class="loop_labels" compatibility="10.2.000" expanded="true" height="82" name="Loop Labels" width="90" x="313" y="34">

        <process expanded="true">

          <operator activated="true" class="write_excel" compatibility="10.2.000" expanded="true" height="103" name="Write Excel" width="90" x="112" y="34">

            <parameter key="excel_file" value="C:/Users/rjones/Downloads/test-%{counter}.xlsx"/>

            <parameter key="file_format" value="xlsx"/>

            <enumeration key="sheet_names"/>

            <parameter key="sheet_name" value="RapidMiner Data"/>

            <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>

            <parameter key="number_format" value="#.0"/>

            <parameter key="encoding" value="UTF-8"/>

          </operator>

          <operator activated="true" class="generate_macro" compatibility="10.2.000" expanded="true" height="82" name="Generate Macro" width="90" x="313" y="85">

            <list key="function_descriptions">

              <parameter key="counter" value="eval(%{counter})+1"/>

            </list>

          </operator>

          <connect from_port="example set" to_op="Write Excel" to_port="input"/>

          <connect from_op="Write Excel" from_port="through" to_op="Generate Macro" to_port="through 1"/>

          <connect from_op="Generate Macro" from_port="through 1" to_port="out 1"/>

          <portSpacing port="source_example set" spacing="0"/>

          <portSpacing port="sink_out 1" spacing="0"/>

          <portSpacing port="sink_out 2" spacing="0"/>

        </process>

      </operator>

      <connect from_op="Retrieve dataset" from_port="output" to_op="Set Macro" to_port="through 1"/>

      <connect from_op="Set Macro" from_port="through 1" to_op="Loop Labels" to_port="example set"/>

      <connect from_op="Loop Labels" from_port="out 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>

MartinLiebig · November 2023