Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Multiple Input into 1 Excel book with multiple output sheets in the same workbook

Highlighted
7 - Meteor

Unsure if this has already been covered. Attempting to pull 4 different query results into 1 workbook but each query needs to have it's own tab. Currently this is what my workflow looks like. The problem i run into is that it believes the file is open simultaneously by the others - is there a tool that may help me run each query in sequence? I attempted the block until done tool, but didn't have much luck. 

Highlighted
Alteryx Certified Partner

Hi @Ben_Kohler ,

 

If all of your queries have the same columns, you can use the output data tool to create one tab for each query. If this doesn't work for you, please share a image of your workflow at least, please.

 

Let me know if that works for you.

Best,

Fernando Vizcaino

 

Highlighted
Alteryx
Alteryx

The approach found here is the easiest way to handle this: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Block-Until-Done-for-Entire-Containers... 

 

You are correct in that the output is attempting to write to the same file simultaneously which is causing your error.

 

"If you are trying to prevent Step Two from OUTPUT to a file, you can use a SUMMARIZE tool to count the # of records in Step One.  Then immediately prior to the OUTPUT in Step Two you can place an APPEND FIELDS tool where the COUNT goes into the bottom anchor.  In the configuration, you can uncheck the COUNT field.  The OUTPUT tool will NOT run until Step One is finished."

 

This should do the trick, but if it still errors you can leverage this technique in combination with block until done. 

Highlighted
7 - Meteor

You could use the Render tool in this case to separate the data.  Before the output, add a table tool and in the group by configuration, select the column that you are using to separate tab names.

 

Next, add a render tool and in the configuration panel, under Separator, select Insert Section Breaks Between Records.  This will create separate tabs for each set. 

 

Input - 

amazz24_2-1579813015359.png

 

Table Tool Configuration - 

amazz24_1-1579812997861.png

 

Render Tool Configuration - 

amazz24_3-1579813164167.png

 

My output now has 4 tabs, data in each tab is as follows:

SheetData1Data2
Sheet1A1
Sheet2B2
Sheet3C3
Sheet4D4

 

Highlighted
7 - Meteor

Hi Fernando,

A screen shot of my workflow was attached. Each query a different set of field headers since each report is slightly different.

Highlighted
7 - Meteor

@Ben_Kohler 

 

Apologies, as my solution will not work in that case either.  I was unable to open the attachment originally, so I made an assumption on what the data looked like.  Now that I can see it, using the table and render tools will not be a viable option as you are not merging the data into one set.

Highlighted
Alteryx
Alteryx

@amazz24 I think that my approach should work for you

Highlighted
7 - Meteor

Hi Brandon,

Thanks for your response. I'm unfamiliar with how the Block until done tool works to be honest. I see that it has 1 input and 3 outputs but unsure how to properly configure it. What I was hoping to do was set these 4 queries up to be sequentially run, and all 4 queries end up on the same workbook but to it's own dedicated tab.

 

Currently looking into the Summarize tool that you described. I'll see if that is a possible solution for my issue.

Highlighted
7 - Meteor

@Ben_Kohler - You might also be able to use the Wait A Second tool, which is part of an Invisio Macro Pack.  See link below.

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Wait-Pause-Between-Processing-Recor...

Highlighted
Alteryx
Alteryx

Give this approach a try

 

multiple file output.png

Labels