How do I determine if a n attribute is a date or date/time type

JMyers

Hi,

I have a process that accepts variable inputs. Field types are unknown at design time. I need to figure out how to test if an attribute is any kind of date type so that I can change them text.

I tried the Date To Nominal operator, but unlike the nominal to polynomial operator it doesn't have an option for all date type fields. 

RolandJones

Hi @JMyers,

Would the following example process provide what you need? It loops over all date_time attributes and converts to nominal.

Thanks,

Roland

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

  <context>

    <input/>

    <output/>

    <macros/>

  </context>

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

    <process expanded="true">

      <operator activated="true" class="retrieve" compatibility="10.4.001" expanded="true" height="68" name="Retrieve LendingClub Loan Data 2007-2011 (27MB)" width="90" x="112" y="34">

        <parameter key="repository_entry" value="//Community Samples/Community Data Sets/Finance and Business/LendingClub Loan Data/LendingClub Loan Data 2007-2011 (27MB)"/>

      </operator>

      <operator activated="true" class="concurrency:loop_attributes" compatibility="10.4.001" expanded="true" height="82" name="Loop date_time" width="90" x="246" y="34">

        <parameter key="attribute_filter_type" value="value_type"/>

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

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

        <parameter key="use_except_expression" value="false"/>

        <parameter key="value_type" value="date_time"/>

        <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="attribute_name_macro" value="loop_attribute"/>

        <parameter key="reuse_results" value="true"/>

        <parameter key="enable_parallel_execution" value="true"/>

        <process expanded="true">

          <operator activated="true" class="date_to_nominal" compatibility="10.4.001" expanded="true" height="82" name="Date to Nominal" width="90" x="45" y="34">

            <parameter key="attribute_name" value="%{loop_attribute}"/>

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

            <parameter key="time_zone" value="SYSTEM"/>

            <parameter key="locale" value="English (United States)"/>

            <parameter key="keep_old_attribute" value="false"/>

          </operator>

          <connect from_port="input 1" to_op="Date to Nominal" to_port="example set input"/>

          <connect from_op="Date to Nominal" 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>

      <connect from_op="Retrieve LendingClub Loan Data 2007-2011 (27MB)" from_port="output" to_op="Loop date_time" to_port="input 1"/>

      <connect from_op="Loop date_time" 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>

RolandJones

Hi @JMyers,

Just so I can propose the right solution here, I just want to confirm: you'd like to take all date and date_time columns and convert to nominal?

Best,

Roland

JMyers

@rjones13

Correct. The basic process is to audit 2 different inputs against each other by doing a field by field comparison. The first steps are to convert all numeric data to nominal and then all nominal data to text. 

The process started failing when I passed in query results that had dates even though the stats show they are nominal fields. 

I have an initial solution using the HandleException operator, but would love to hear more ideas. Testing if something is a date and then handeling it one way or another is something I often need to do. 

Thanks.

RolandJones

Hi @JMyers,

Would the following example process provide what you need? It loops over all date_time attributes and converts to nominal.

Thanks,

Roland

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

  <context>

    <input/>

    <output/>

    <macros/>

  </context>

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

    <process expanded="true">

      <operator activated="true" class="retrieve" compatibility="10.4.001" expanded="true" height="68" name="Retrieve LendingClub Loan Data 2007-2011 (27MB)" width="90" x="112" y="34">

        <parameter key="repository_entry" value="//Community Samples/Community Data Sets/Finance and Business/LendingClub Loan Data/LendingClub Loan Data 2007-2011 (27MB)"/>

      </operator>

      <operator activated="true" class="concurrency:loop_attributes" compatibility="10.4.001" expanded="true" height="82" name="Loop date_time" width="90" x="246" y="34">

        <parameter key="attribute_filter_type" value="value_type"/>

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

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

        <parameter key="use_except_expression" value="false"/>

        <parameter key="value_type" value="date_time"/>

        <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="attribute_name_macro" value="loop_attribute"/>

        <parameter key="reuse_results" value="true"/>

        <parameter key="enable_parallel_execution" value="true"/>

        <process expanded="true">

          <operator activated="true" class="date_to_nominal" compatibility="10.4.001" expanded="true" height="82" name="Date to Nominal" width="90" x="45" y="34">

            <parameter key="attribute_name" value="%{loop_attribute}"/>

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

            <parameter key="time_zone" value="SYSTEM"/>

            <parameter key="locale" value="English (United States)"/>

            <parameter key="keep_old_attribute" value="false"/>

          </operator>

          <connect from_port="input 1" to_op="Date to Nominal" to_port="example set input"/>

          <connect from_op="Date to Nominal" 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>

      <connect from_op="Retrieve LendingClub Loan Data 2007-2011 (27MB)" from_port="output" to_op="Loop date_time" to_port="input 1"/>

      <connect from_op="Loop date_time" 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>

JMyers

Thanks. This seems to work.