10 December 2017

When I race on the track my coach encourages me to stick close to the rail and stay in lane one whenever I can. Overtaking round the bends adds on extra distance that could affect your final time and position. The same mind-set can be applied to Alteryx by seeking to cut corners and make time savings wherever possible to get workflows to run faster.My team realised that querying relational databases was a bottleneck in our Alteryx workflows so we started looking for ways to reduce the frequency of queries. We found that the same queries were being run by several workflows on the same day, even though the data only needed to be refreshed monthly. So we designed a macro to feed in the data that would either input an existing yxdb file from a directory (if recent enough) or create a new yxdb file from the database then input that file. In some cases this then saved over an hour of time from a workflow.Here's how to set it up:
2. Create the macro below:
3. Insert this macro in your workflow instead of using the input data tool
- Create a workflow that inputs your database query then outputs a yxdb file with the creation date appended. Adding the creation date is as simple as adding a formula with datetimenow then ticking the take file name from field and unticking keep field in output.

- Set the directory tool to look in the folder where your yxdb files are being written
- Parse the creation date and sort descending
- Take the first one (most recent)
- If that record is older than n days then pass through the conditional runner. Set up the conditional runner to run the workflow which queries the database and writes a new yxdb. This will only happen if a record is passed through the tool. To download the conditional runner, you need to download the Crew macro pack
- Select only the full path
- Union together the two paths; setting a specific output order: #1,#2
- Select the first record. This means that it will take the newly created file path if one was created or take the most recent file path if not.
- Use the dynamic input tool to download the yxdb.
