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.
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:
- Click Input Data Source Template to define your template data source, include the arbitrary filter date as pictured below;
Add the WHERE clause as a filter on the date.
- Click OK, then Click Refresh. Ensure data appears in the Preview window as this confirms that your Date format has been recognised!
Click Ok to close the menu/box
- 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.
Note: We return to this section later.
Formatting the Replacement Field:
- To format the incoming/filter Date field (Replacement Field), add a Formula tool
in front of the Dynamic Input tool. - 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.
- Now go back and re-configure the Dynamic Input tool; Update the Replacement Field with the newly created field.
- Note: “01-Dec-2017” is not the same as “01-DEC-2017”, so synchronise your Cases!
- Add as many Browse tools to watch what is happening.
