Filter rows, by columns max values

Kuyu

I have 2 date columns, startDate and endDate.
I need to filter out the endDate by the min of the startDate.

For example, if I have min start date of 1.1.2014 I cant have endDate of 1.1.2013.

You have an idea how can I do it? 

David_A

Hi @Kuyu,

got it. In this case the Extract Macro operator is very helpful, as it can give you statistics about an specific attribute. In this case the only extra step is that dates are extracted in milliseconds since epoch (the most common way computer handle internally date and time values), so we need to transform the date_end column as well for comparison.

Best,

David

<process version="9.5.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.5.001" 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="utility:create_exampleset" compatibility="9.5.001" expanded="true" height="68" name="Create ExampleSet" width="90" x="45" y="34">
        <parameter key="generator_type" value="attribute functions"/>
        <parameter key="number_of_examples" value="19"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions">
          <parameter key="Date_start" value="date_set(date_now(), floor(30*(rand()-1)),DATE_UNIT_DAY)"/>
          <parameter key="Date_end" value="date_set(date_now(), floor(35*(rand()-1)),DATE_UNIT_DAY)"/>
        </list>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration">
          <parameter key="Date_start" value="linear.0\.0.50\.0"/>
          <parameter key="Date_end" value="linear.0\.0.50\.0"/>
        </list>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="column_separator" value=","/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="extract_macro" compatibility="9.5.001" expanded="true" height="68" name="Extract Macro" width="90" x="246" y="34">
        <parameter key="macro" value="min_date"/>
        <parameter key="macro_type" value="statistics"/>
        <parameter key="statistics" value="min"/>
        <parameter key="attribute_name" value="Date_start"/>
        <list key="additional_macros"/>
      </operator>
      <operator activated="true" class="filter_examples" compatibility="9.5.001" expanded="true" height="103" name="Filter Examples (2)" width="90" x="447" y="34">
        <parameter key="parameter_expression" value="eval(%{min_date})&lt; date_millis(Date_end)"/>
        <parameter key="condition_class" value="expression"/>
        <parameter key="invert_filter" value="false"/>
        <list key="filters_list"/>
        <parameter key="filters_logic_and" value="true"/>
        <parameter key="filters_check_metadata" value="true"/>
        <description align="center" color="transparent" colored="false" width="126">The min_date macro is extracted as milliseconds since epoch, so we transform the date_end as well to this format</description>
      </operator>
      <operator activated="false" class="filter_examples" compatibility="9.5.001" expanded="true" height="103" name="Filter Examples" width="90" x="447" y="340">
        <parameter key="parameter_expression" value="date_diff(Date_start,Date_end)&lt;0"/>
        <parameter key="condition_class" value="expression"/>
        <parameter key="invert_filter" value="false"/>
        <list key="filters_list"/>
        <parameter key="filters_logic_and" value="true"/>
        <parameter key="filters_check_metadata" value="true"/>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Extract Macro" to_port="example set"/>
      <connect from_op="Extract Macro" from_port="example set" to_op="Filter Examples (2)" to_port="example set input"/>
      <connect from_op="Filter Examples (2)" 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"/>
      <description align="center" color="yellow" colored="false" height="105" resized="false" width="180" x="199" y="127">Minimum start_date is December 1st</description>
    </process>
  </operator>
</process>

David_A

Hi @Kuyu,

you can use the expression parser to compare the two dates and get the difference between the two dates. There is a function called date_diff() if the difference is less than 0, the example is invalid.

See the attached process on how to use the expression builder to filter examples.

Best,
David

<process version="9.5.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.5.001" 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="utility:create_exampleset" compatibility="9.5.001" expanded="true" height="68" name="Create ExampleSet" width="90" x="112" y="34">
        <parameter key="generator_type" value="attribute functions"/>
        <parameter key="number_of_examples" value="19"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions">
          <parameter key="Date_start" value="date_set(date_now(), floor(30*(rand()-1)),DATE_UNIT_DAY)"/>
          <parameter key="Date_end" value="date_set(date_now(), floor(35*(rand()-1)),DATE_UNIT_DAY)"/>
        </list>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration">
          <parameter key="Date_start" value="linear.0\.0.50\.0"/>
          <parameter key="Date_end" value="linear.0\.0.50\.0"/>
        </list>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="column_separator" value=","/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="filter_examples" compatibility="9.5.001" expanded="true" height="103" name="Filter Examples" width="90" x="313" y="34">
        <parameter key="parameter_expression" value="date_diff(Date_start,Date_end)&lt;0"/>
        <parameter key="condition_class" value="expression"/>
        <parameter key="invert_filter" value="false"/>
        <list key="filters_list"/>
        <parameter key="filters_logic_and" value="true"/>
        <parameter key="filters_check_metadata" value="true"/>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Filter Examples" to_port="example set input"/>
      <connect from_op="Filter Examples" from_port="example set output" to_port="result 1"/>
      <connect from_op="Filter Examples" from_port="unmatched example set" to_port="result 2"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
      <portSpacing port="sink_result 3" spacing="0"/>
    </process>
  </operator>
</process>

Kuyu

Hey @David_A

I understand what you did, but I need ti now per row,
I need it to be the min of all the dataset of date_start and only then comper to date_end

In you example you comper each row,

David_A

Hi @Kuyu,

got it. In this case the Extract Macro operator is very helpful, as it can give you statistics about an specific attribute. In this case the only extra step is that dates are extracted in milliseconds since epoch (the most common way computer handle internally date and time values), so we need to transform the date_end column as well for comparison.

Best,

David

<process version="9.5.001">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="9.5.001" 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="utility:create_exampleset" compatibility="9.5.001" expanded="true" height="68" name="Create ExampleSet" width="90" x="45" y="34">
        <parameter key="generator_type" value="attribute functions"/>
        <parameter key="number_of_examples" value="19"/>
        <parameter key="use_stepsize" value="false"/>
        <list key="function_descriptions">
          <parameter key="Date_start" value="date_set(date_now(), floor(30*(rand()-1)),DATE_UNIT_DAY)"/>
          <parameter key="Date_end" value="date_set(date_now(), floor(35*(rand()-1)),DATE_UNIT_DAY)"/>
        </list>
        <parameter key="add_id_attribute" value="false"/>
        <list key="numeric_series_configuration">
          <parameter key="Date_start" value="linear.0\.0.50\.0"/>
          <parameter key="Date_end" value="linear.0\.0.50\.0"/>
        </list>
        <list key="date_series_configuration"/>
        <list key="date_series_configuration (interval)"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss"/>
        <parameter key="time_zone" value="SYSTEM"/>
        <parameter key="column_separator" value=","/>
        <parameter key="parse_all_as_nominal" value="false"/>
        <parameter key="decimal_point_character" value="."/>
        <parameter key="trim_attribute_names" value="true"/>
      </operator>
      <operator activated="true" class="extract_macro" compatibility="9.5.001" expanded="true" height="68" name="Extract Macro" width="90" x="246" y="34">
        <parameter key="macro" value="min_date"/>
        <parameter key="macro_type" value="statistics"/>
        <parameter key="statistics" value="min"/>
        <parameter key="attribute_name" value="Date_start"/>
        <list key="additional_macros"/>
      </operator>
      <operator activated="true" class="filter_examples" compatibility="9.5.001" expanded="true" height="103" name="Filter Examples (2)" width="90" x="447" y="34">
        <parameter key="parameter_expression" value="eval(%{min_date})&lt; date_millis(Date_end)"/>
        <parameter key="condition_class" value="expression"/>
        <parameter key="invert_filter" value="false"/>
        <list key="filters_list"/>
        <parameter key="filters_logic_and" value="true"/>
        <parameter key="filters_check_metadata" value="true"/>
        <description align="center" color="transparent" colored="false" width="126">The min_date macro is extracted as milliseconds since epoch, so we transform the date_end as well to this format</description>
      </operator>
      <operator activated="false" class="filter_examples" compatibility="9.5.001" expanded="true" height="103" name="Filter Examples" width="90" x="447" y="340">
        <parameter key="parameter_expression" value="date_diff(Date_start,Date_end)&lt;0"/>
        <parameter key="condition_class" value="expression"/>
        <parameter key="invert_filter" value="false"/>
        <list key="filters_list"/>
        <parameter key="filters_logic_and" value="true"/>
        <parameter key="filters_check_metadata" value="true"/>
      </operator>
      <connect from_op="Create ExampleSet" from_port="output" to_op="Extract Macro" to_port="example set"/>
      <connect from_op="Extract Macro" from_port="example set" to_op="Filter Examples (2)" to_port="example set input"/>
      <connect from_op="Filter Examples (2)" 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"/>
      <description align="center" color="yellow" colored="false" height="105" resized="false" width="180" x="199" y="127">Minimum start_date is December 1st</description>
    </process>
  </operator>
</process>