Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Alteryx filter output to multiple excel tabs in one excel workbook

Bhavika
8 - Asteroid

Hi There,

 

I'm badly stuck in a situation where i'm trying to output filter results to multiple tabs in an excel workbook, which i name dynamically using the Output tool configuration. So, i use the first filter results to create an excel output and use block until done tool for to output further filter results. Below is a snapshot of my workflow and the error i receive( i have tried changing the output options to Overwrite File, Create new sheet, Drop file) but nothing seems to give me what i'm looking for. 

 

Workflow

output to multiple excel tabs in 1 workrbook.PNGerror.PNG

 

Also, my company doesn't allow me to install crew macros package so can't use Parallel block until done.

Please help as i have spent days figuring out what do i do :(

 

TIA.

9 REPLIES 9
Aidan_K
11 - Bolide

Hi @Bhavika

Try changing your first filter ([Balance_USD]!=0 to come from your first block until done (No. 1) instead of straight from the select tool.

this should fix the writing priority issue

 

if you attach the workflow (or part of it) I can update for you too if you like

Rgds,
Aidan

 

 

JordanB
Alteryx
Alteryx

Hi @Bhavika

 

What version of Alteryx are you on?  (Help>About)

 

Looking at your workflow I would build you stream off the first block until done tool. Your top workflow needs to come out of '1'. The second stream comes out of '2'

 

I used this workflow for a reference: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Bug-Multiple-Output-Data-to-Excel-Over...

 

Best,

 

Jordan

 

 

Bhavika
8 - Asteroid

 

@Aidan_K Due to IT permissions, i cam't attach my workflow or related files here. So, posting the snapshots. 

 

@JordanB  I'm using Alteryx Admin Designer 2018.3.4.51585. 

 

 

As suggested, I have made the changes. However, still face the same issue.

 

I'm also attaching a snapshot of my 1st output tool configuration. For the 2nd, the only change i made was 'Overwrite sheet(Drop).This creates the output out of 1st output tool. But, 2nd output tool errors out.

 

The 'TabName' column referred in the Output tool configuration has the below formula:

"H:\Alteryx\Control Check Automation\C3 Check\Output\C3_Codeblock_mapping_check_BD1_" + left(datetimeformat([COB_DATE],"%B"),3)+ "_new.xlsx|||" + "Unassigned with balances" 

 

For the 2nd output tool, the 'TabName' column has just the sheet name changed:

"H:\Alteryx\Control Check Automation\C3 Check\Output\C3_Codeblock_mapping_check_BD1_" + left(datetimeformat([COB_DATE],"%B"),3)+ "_new.xlsx|||" +"Peaks Data with SAP AC"

 

 

Block until done updated.PNGOutput tool configuration.PNG

 

 

Also, i did refer to this suggested post but couldn't find a solution there. :(

 

 

Aidan_K
11 - Bolide

I think I have replicated and have it working now. See attached sample workflow screenshot and also output optionsCapture.JPGCapture2.JPG

Bhavika
8 - Asteroid
Thanks a lot for this.
Couple of questions:
1. I see in your snapshot that for the 2nd output you take a data stream out of the filter tool for the 2nd output generation will it make a difference to the block untill tool functionality if i take it from the 1st block until done tool right after the select tool into the 2nd filter since, the one done in yours would impact my results.
2. I need 6-7 outputs like these. So, my final output would be the excel with 6-7 alteryx filter results in different tabs. Would block until done be able to handle this scenario?

Lastly,could you please share the workflow as an attachment.So, that way i'll be able to compare your workflow with mine in a deeper way to understand where i went wrong.
Well, apparantely my company doesn't allow me to upload any files but download is permitted.Weird!!
Thanks again :)
Bhavika
8 - Asteroid

@Aidan_K I tried doing the same thing as yours. However, still get the same error. Could you please share your workflow.  Did you use 'Overwrite sheet or Overwrite File as output options in both the output tools?

 

Aidan_K
11 - Bolide

Hi @Bhavika

 

I am attaching my sample workflow for your review.

Here you can see the same results created with the block in both (different) places which should help accommodate your results.

 

I hope this helps :)

Regards,

Aidan 

Bhavika
8 - Asteroid

@Aidan_K Thanks a lot. It worked like a charm! But, i noticed something weird so Alteryx outputs to Desktop path but errors out when output to Network path. That's where i was going wrong. Now, i have changed the location of my output files to Desktop.

 

Thanks again :)

Aidan_K
11 - Bolide

Your welcome, I'm glad it is resolved for you.

 

Separately, We had instances in the past where certain Alteryx outputs, in some cases, required the full network address or Universal Naming Convention (UNC) in the output path and not just the short name (Alias) - I think in your example H:\Alteryx\. If you specify the full network path it may work for you, its worth a try :)

 

Regards,

Aidan

Labels