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 https://karthikvankadara.com/2017/12/20/incremental-data-load-using-alteryx/ in which he clearly explains how to use the Dynamic Input tool for incremental data loads.

Tool_1

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:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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