Alteryx – Incremental Data Load – Dynamic Input Tool with Oracle Data Source

This blog can be seen as a follow on from Karthik V’s blog at in which he clearly explains how to use the Dynamic Input tool for incremental data loads.


Incremental data load auditing is essential to our Oracle Data Warehouse as we need to update our Target tables monthly from the Finance system on a particular date; I had been trolling the cloud to find the best way to use Alteryx Dynamic Input tool for Incremental loads.

I had tried Karthik V’s method on our data warehouse and it gave me numerous errors, such as:

  • “Error literals to do not match”
  • “Not a valid date format”

I then realised that using the Alteryx date format of ISO format yyyy-mm-dd HH:MM:SS will not be a match for Oracle Date Type.

Follow these simple steps and it should work perfect for you.

Formatting the Dynamic Input tool:

  1. Click Input Data Source Template to define your template data source, include the arbitrary filter date as pictured below;Tool_2

Add the WHERE clause as a filter on the date.

  1. Click OK, then Click Refresh. Ensure data appears in the Preview window as this confirms that your Date format has been recognised!      Tool_3                                                         Click Ok to close the menu/box
  1. Select Modify SQL Query and choose the default clause string from the drop down box. Then select the replacement Field; which is the date that will actually filter the data above.                                                                                                                                      Tool_4                                                 Note: We return to this section later.

Formatting the Replacement Field:

  1. To format the incoming/filter Date field (Replacement Field), add a Formula tool Tool_5 in front of the Dynamic Input tool.
  2. Create a new column and set Data Type to STRING. Add the Date Function and format the Replacement Field to match your Where Clause filter Date type. Tool_6
  1. Now go back and re-configure the Dynamic Input tool; Update the Replacement Field with the newly created field. Tool_7
  1. Note: “01-Dec-2017” is not the same as “01-DEC-2017”, so synchronise your Cases! Tool_8
  2. Add as many Browse tools to watch what is happening. Tool_9



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s