Hello. Currently I am writing a table to SQL Server from a flat file. When a new flat file comes in, I have a process created to update the current table. I am using the 'Update Database' operator. I would like to have a 'Timestamp' column that will update when that row is updated with new data. I tried to create a 'Timestamp' column using 'Generate Attributes' but of course when I update the table it updates every row. My 'update' process is pasted below.
TIA
<?xml version="1.0" encoding="UTF-8"?><process version="8.2.000">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="8.2.000" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="open_file" compatibility="8.2.000" expanded="true" height="68" name="Open File" width="90" x="45" y="340">
<parameter key="filename" value="Z:\Project Cost Drop\Copy of Project Costs - New Format FY11-FY18 TEST.xlsx"/>
</operator>
<operator activated="true" class="read_excel" compatibility="8.2.000" expanded="true" height="68" name="Read Excel" width="90" x="45" y="442">
<parameter key="excel_file" value="Z:\Project Cost Drop\Project Costs - New Format FY11-FY18.xlsx"/>
<parameter key="imported_cell_range" value="A1:L50000"/>
<parameter key="first_row_as_names" value="false"/>
<list key="annotations"/>
<list key="data_set_meta_data_information">
<parameter key="0" value="Year.true.polynominal.attribute"/>
<parameter key="1" value="B.false.polynominal.attribute"/>
<parameter key="2" value="Month.true.polynominal.attribute"/>
<parameter key="3" value="D.false.polynominal.attribute"/>
<parameter key="4" value="Project_ID.true.polynominal.attribute"/>
<parameter key="5" value="Project_Name.true.polynominal.attribute"/>
<parameter key="6" value="Hours.true.numeric.attribute"/>
<parameter key="7" value="Labor_Costs.true.numeric.attribute"/>
<parameter key="8" value="Pro_Srvcs.true.numeric.attribute"/>
<parameter key="9" value="Other_Purchases.true.numeric.attribute"/>
<parameter key="10" value="Capex.true.numeric.attribute"/>
<parameter key="11" value="Hosted.true.numeric.attribute"/>
</list>
</operator>
<operator activated="true" class="subprocess" compatibility="8.2.000" expanded="true" height="82" name="Concatenate" width="90" x="45" y="544">
<process expanded="true">
<operator activated="true" class="generate_concatenation" compatibility="8.2.000" expanded="true" height="82" name="Generate Concatenation" width="90" x="45" y="34">
<parameter key="first_attribute" value="Year"/>
<parameter key="second_attribute" value="Month"/>
</operator>
<operator activated="true" class="generate_concatenation" compatibility="8.2.000" expanded="true" height="82" name="Generate Concatenation (2)" width="90" x="179" y="34">
<parameter key="first_attribute" value="Year_Month"/>
<parameter key="second_attribute" value="Project_ID"/>
</operator>
<connect from_port="in 1" to_op="Generate Concatenation" to_port="example set input"/>
<connect from_op="Generate Concatenation" from_port="example set output" to_op="Generate Concatenation (2)" to_port="example set input"/>
<connect from_op="Generate Concatenation (2)" from_port="example set output" to_port="out 1"/>
<portSpacing port="source_in 1" spacing="0"/>
<portSpacing port="source_in 2" spacing="0"/>
<portSpacing port="sink_out 1" spacing="0"/>
<portSpacing port="sink_out 2" spacing="0"/>
</process>
</operator>
<operator activated="true" class="subprocess" compatibility="8.2.000" expanded="true" height="82" name="Select & Rename" width="90" x="45" y="646">
<process expanded="true">
<operator activated="true" class="select_attributes" compatibility="8.2.000" expanded="true" height="82" name="Select Attributes (2)" width="90" x="45" y="34">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attributes" value="Capex|Hosted|Hours|Labor_Costs|Month|Other_Purchases|Pro_Srvcs|Project_ID|Project_Name|Year|A_C_E|Year_Month_Project_ID"/>
</operator>
<operator activated="true" class="rename" compatibility="8.2.000" expanded="true" height="82" name="Rename" width="90" x="313" y="85">
<parameter key="old_name" value="Year_Month_Project_ID"/>
<parameter key="new_name" value="ACE"/>
<list key="rename_additional_attributes"/>
</operator>
<connect from_port="in 1" to_op="Select Attributes (2)" to_port="example set input"/>
<connect from_op="Select Attributes (2)" from_port="example set output" to_op="Rename" to_port="example set input"/>
<connect from_op="Rename" from_port="example set output" to_port="out 1"/>
<portSpacing port="source_in 1" spacing="0"/>
<portSpacing port="source_in 2" 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="8.2.000" expanded="true" height="82" name="Generate Attributes" width="90" x="246" y="544">
<list key="function_descriptions">
<parameter key="Updated_Date" value="date_now()"/>
</list>
</operator>
<operator activated="true" class="set_role" compatibility="8.2.000" expanded="true" height="82" name="Set Role" width="90" x="380" y="544">
<parameter key="attribute_name" value="ACE"/>
<parameter key="target_role" value="id"/>
<list key="set_additional_roles"/>
</operator>
<operator activated="true" class="jdbc_connectors:update_database" compatibility="8.2.000" expanded="true" height="68" name="Update Database" width="90" x="514" y="544">
<parameter key="connection" value="RapidMiner_02"/>
<parameter key="table_name" value="Fin_Extract_Stage"/>
<parameter key="attribute_filter_type" value="single"/>
<parameter key="attribute" value="ACE"/>
<parameter key="attributes" value="|Updated_Date|Pro_Srvcs|Other_Purchases|Labor_Costs|Hours|Hosted|Capex"/>
</operator>
<operator activated="false" class="jdbc_connectors:read_database" compatibility="8.2.000" expanded="true" height="68" name="Read Database" width="90" x="45" y="34">
<parameter key="connection" value="RapidMiner_02"/>
<parameter key="query" value="SELECT * FROM "dbo"."Fin_Extract_Stage""/>
<enumeration key="parameters"/>
</operator>
<operator activated="false" class="append" compatibility="8.2.000" expanded="true" height="68" name="Append" width="90" x="179" y="34"/>
<operator activated="false" class="remove_duplicates" compatibility="8.2.000" expanded="true" height="103" name="Remove Duplicates" width="90" x="313" y="34">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attributes" value="ACE"/>
<parameter key="invert_selection" value="true"/>
<parameter key="include_special_attributes" value="true"/>
</operator>
<connect from_op="Open File" from_port="file" to_op="Read Excel" to_port="file"/>
<connect from_op="Read Excel" from_port="output" to_op="Concatenate" to_port="in 1"/>
<connect from_op="Concatenate" from_port="out 1" to_op="Select & Rename" to_port="in 1"/>
<connect from_op="Select & Rename" from_port="out 1" to_op="Generate Attributes" to_port="example set input"/>
<connect from_op="Generate Attributes" from_port="example set output" to_op="Set Role" to_port="example set input"/>
<connect from_op="Set Role" from_port="example set output" to_op="Update Database" to_port="input"/>
<connect from_op="Update Database" from_port="through" 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>
