Altair RISE
A program to recognize and reward our most engaged community members
Nominate Yourself Now!
Home
Discussions
Community Q&A
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?
Find more posts tagged with
AI Studio
Dates + Times
Filtering
Accepted answers
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})< 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)<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>
All comments
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)<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})< 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)<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>
Quick Links
All Categories
Recent Discussions
Activity
Unanswered
日本語 (Japanese)
한국어(Korean)
Groups