Using An Output as An Input in Alteryx

Using a workflow output as an input in the same workflow can be tricky. How do you make sure the output is completely written before being used as an input? A few months ago, I showed how the CReW macro tool, Parallel Block Until Done, can be used to control the order of operations in a workflow so that the output is fully written before the input is used. The CReW macro creator, Mark Frisch, reached out to me recently to tell me that my solution wasn’t wrong, but that there was a simpler way to achieve the same task. He bestowed some sage Alteryx wisdom upon me, and now I want to share it with you. Thanks Mark! Read on to learn more.

Blog or Video

Solution Components

I use a dummy workflow to explain the solution. My actual workflow is long and complicated and difficult to screenshot. For the purpose of example, assume the main process of the workflow is doing whatever heavy lifting you need it to do. Then, it writes to an Output. And, that output needs to be used in another part of the workflow as an input. If you want to know why you would ever want to do this, skip to the bottom.

There are two ways you can make this happen. I’ve shown both below.

High-Level Summary WF1

Solution Components

  1. Block Unit Done
  2. Summarize
  3. Append Fields
  4. Text Input
  5. Dynamic Input

The crux of this solution is the Block Until Done tool to make sure the Output writes completely before moving on to the next step in the workflow. The rest of the tools create flow. The count created in the Summarize tool, which is added to the data set in the Text Input tool by the Append Fields tool isn’t actually used at all. But it creates a flow path. The Text Input tools holds the file path used by the Dynamic Query to ingest the output as an input. Without the flow created by the Summarize and Append Fields, it would look like this (screenshot below), which doesn’t work. You might be thinking, why not connect directly to the Dynamic Input tool? And, that is the second solution. The details of each of the tools in this solution are shown below.

Text input is one record/cell of data. It tells Alteryx where to pull the input from.
The Summarize tool creates a count that is not used.
The Append Fields tool appends the count to the Text Input tool contents.
append results pane
This is the result.
The Dynamic Input picks up the location of the output file that needs to be read in as an input.

High-Level Summary WF2

Another option is to skip the Summarize and Append Fields tools and go more directly to the Dynamic Query. To do that, I used a Sample tool to take one and only one record from the workflow stream and feed it into the Dynamic Query. Then, the Formula tool provides the file path to find the input file. Block Until Done still ensures the output is written before it is used as an input.

I think I struggled with this problem more before I knew about using Dynamic Query. If you only ever used Input tools and didn’t know how to use Dynamic Queries, this would be a much more complicated problem to solve.

HTML

But Why?

Now, before closing this post, I know some of you are probably wondering why you would ever want to do this in the first place. There are several use cases I can think of. This would work for writing worksheets to Excel in a specific order. You might use it if you need to use an ending balance as a starting balance. In my particular case, I needed to do the following….

  1. Bring a list of wells used in a process last month (before step 5 overwrites the data).
  2. Bring in a new list of wells for this month’s process.
  3. Compare the two lists.
  4. Run the monthly process.
  5. Write a list of wells to an output to be used for comparison next month (after step 3).

And, the moral of this post is that there are multiple ways to stop and start the process. And, if you didn’t see my original post, check it out here.

Alteryx Version

Content created with Alteryx 2020.3.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.