Alteryx designer Discussions

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

Add Headers & output in a particular Excel tab

Highlighted
8 - Asteroid

Hi @grossal @DavidP 

 

I have 3 outcomes of a Alteryx Workflow. I need to add different headers in each outcome and then need to extract them all in a single excel tab and in a particular cell.

 

Example is attached herewith.

 

Thanks in advance.

 

Mohit

Highlighted
13 - Pulsar

Hi @mohit9garg,

 

maybe @DavidP has a better idea, because I am not quite sure what's the best way to achieve this.

 

Exporting data to specific cells or ranges is no problem with the output tool, but the header issue will remain. Usually I'd do all the formatting in the Reporting tools but I don't see a way to write the Export to a specific cell with the Render tool.

 

Sure, you could use two Output-Tools and write Header and Data differently, but I think you are not able to have Header Cells that span over two columns (or more) with that. You would also need a Block-Until-Done tool for this. It's probably the closest to your solution from my point of view. Maybe David has something in mind.

 

Sorry for the no-show to your tag last time. I have been busy preparing Alteryx Workshops at work lately and did not find the time to check my community notifications. 

 

Best

Alex

Highlighted
15 - Aurora

You have a couple of things to consider.

 

If you're writing to an existing Excel file, you have to use the Output data tool, since the Render tool will completely overwrite any existing Excel file.

 

The Output data tool can't create merged cells, but can write to them if they already exist. Also, since 2020.1 you can now retain any cell formatting when overwriting data in a sheet or range.

 

If you were to use Output data tools to write data to specific cells, you'd have the generate all of it as data rather than column headers and set the Output data tool to write the data only without headers.

 

I'm attaching an example of how you can create merged headers with a combination of Table and Text tools. You can also use empty Text tools (containing just a space) to insert rows and columns between and above tables. It will be tricky to manipulate the tools to get the data to the exact cells you want, but it's doable.

 

DavidP_0-1590015220269.png

Hope this helps.

 

Highlighted
8 - Asteroid

Thanks @DavidP & @grossal 

 

Just to clarify, what is the exact problem with using OUTPUT tool-

 

1) Adding headers; or

2) Adding header in merged cells

 

If problem is only 2), then we can write the header in one cell (un-merged).

 

Actually, my Alteryx workflow is giving me 20+ outcomes and I want all of them in a single tab. So to differentiate them, I want to add some header or reference against each of them.

Highlighted
13 - Pulsar

My point was 2) in combination with predefined range that you want to write to. If you are fine with single cell headers, you could just use the new Output options:

 

grossal_0-1590049724949.png

 

You could do this in a couple ways. I'd probably use a Batch Macro. Union all the data with file name included. You split your data again in the batch macro. A Block Until Done is needed to get separated writes for Header/Data. For this to work properly you would need Range-Definition for each source, but you would need this anyway.

 

What do you think?

 

 

Best

Alex

Highlighted
8 - Asteroid

Thanks Alex @grossal 

 

This is getting bit technical for a beginner like me

 

I am ok with any of the easier option. Could you please build a sample workflow for me.

 

FYI, I am using 2018.4 Alteryx version.

 

Kind Regards,

Mohit

 

Highlighted
13 - Pulsar

Ohh. Sorry for the technical terms. I personally have never used 2018.4 (I started using Alteryx 6 month ago with 2019.4). I have to check if I can get access to a 2018.4 version and see what Tools/Features are available there.

 

Have you tested @DavidP solution? He also attached a workflow for you to test it out.

Highlighted
8 - Asteroid

@grossal 

 

Sure Alex; let me also try @DavidP solution.

 

thanks

Highlighted
11 - Bolide

Hi @mohit9garg 

 

I have provided an ugly workaround for your problem. I used an Visual Layout tool and added empty Report Text to create rows and columns. Please use as per your liking. Sample workflow attached.

Highlighted
8 - Asteroid

thanks @deviseetharaman  let me follow this workaround.

Labels