Alteryx Automation Series: PreSQL in Output Tools
Welcome to the Alteryx Automation mini series on Intersections and Overlaps. Starting fresh with Alteryx Server and Designer within a new organisation reminded me of all the small useful things which I had set up that I took for granted. So this mini series is for me to put pen to paper and share those useful tips with you all.
This first one is about having a lookup table within your database of the current business period and the previous one and why that is useful when data engineering with Alteryx and Alteryx Server. There are many ways to do this and I have kept this example simple to follow along, but for some organisations dates and times don’t follow monthly patters and cannot simply use MONTH() or YEAR() functions they have to build it on 4 weekly patterns or even 3.
When running automation sometimes its easier if you can delete the data of the current period and replace it with the fresh data from this period. Well you are in luck as this blog will share all.
Ideally you need a lookup table in your database which looks like this:
There are many ways to achieve this here is an example which supports one where we lookup from the business data itself. This depends entirely on your business period. If your business does follow a simple month pattern then you can use a formula tool to create it and it it only needs to be run once a month on the first of the month. However if you are running a complex period and require regular change, run the process once a day to keep it fresh and create those rules but ideally you are trying to achieve the table above in a location your can access with Alteryx and Alteryx Server.
Internally you can include all the different ways you record your data in each system in this lookup table to ensure you have them available for each one you interact with. This becomes powerful in the next part for when you are updating your data. For example one system may record this business period differently to another, if so include both and describe them using their system name so that when you are using it in the next part it is simple to select the correct one.
Now we have our lookup table handy, we can put it to some use:
Within the OUTPUT tool configuration lets look at the three elements which are important to writing data in a database where you want to delete the previous data and reload it with the current view. I will explain the purple highlighted areas on the left in the image above:
- Connection: ensure you are connected to the database and selected the table you are updating the data in.
- Output Options: Select here the action you would like to take place with your data after your PRE SQL statement. Here I have selected APPEND EXISTING because I want the data to be added to the table in the database, not drop the table, etc.
- Pre Create SQL Statement: This is where it all comes together, PreSQL statements are statements you can run before any actions take place with the data on your database. Think about the action you want to happen when you add more data to your table. Do you want to add more data to data which already exists? No, you need to delete the current data first… so let’s take a look at how we might do that.
Delete from “TABLE” where YYYYMM in (select YYYYMM from “LOOKUP_TABLE” where DESCRIPTION = ‘CURRENT_MONTH’)A Simple PRE SQL STATEMENT in Alteryx
Now looking at these two elements side by side, we have a lookup table that stays up to date with the current month and previous month codes and a PreSQL statement which enables us to grab the current_month code and remove the data for the current month before inserting the latest view of that month. Let’s also note that I have simplified this for the blog and not used internal codes for sales periods which are more complex than that of a simple YYYYMM format. Therefore having this lookup table enables you to quickly grab the detail you need without having to write the same code in multiple places. If you did need to update this code now, you only have to go to the one workflow to update rather than multiple workflows. I know right, always thinking of my future self!
If you wanted to create a second workflow or run it once for the replacement of the previous months data, you only have to change description in the PreSQL from the ‘CURRENT_MONTH’ to ‘PREVIOUS_MONTH’ and if you needed to change the previous and current month’s worth of data each time you only need to change the symbol to greater than the previous month and that is possible too!
There are many ways to use and benefit from this but for me this was one of the first things I implemented as it makes sense to have control over the data I was pushing into the database through my automation workflows using Alteryx Server.
The next post in my automation series will show why writing a record into a table every time you run some automation in your database for your end users is the right thing to do.