Multiple Data Files > In a Tabbed Excel File, Multiple Reports, Append Data Source, Email

[Scott_Snowman]

Hi Alteryx users, I have a feeling there's a simple way to work with this problem that I just am not seeing.

I work with projects where we supply two parameter files to vendors, and they execute a project based on those parameters. They give back one data file.

I've been building a macro that takes the three files together and does a QA analysis to verify how well the returned data matches the parameters. There are multiple types of QA review, so for any given project I could potentially generate up to 10 slices of data to assess which records are non-compliant.

I currently have all 10 analyses being performed on the files, and using the technique described here I can create an output file with up to 10 tabs depending on which analyses produce failing results. So far, so good.

However I'd like to append the original data the vendor provided. When I tried passing it through the Render tool as another table to the Union tool, the workflow slowed down due to the Render of the original data. A relatively small file took 4 minutes, which I realize isn't a long period of time. However, the analysis itself only takes <30 seconds.

Ideally I could just output the data I've input to a new tab in the same Excel file that's output by the Render tool. But timing is of course an issue here. The Block Until Done tool appears to be the right solution, and I've used it before when I wanted to take one input stream and do two sequential things to it. However, this is two separate input streams and I can't figure out how to tell the Output to wait until the Render is done.

Any suggestions?

Edit: Once done, I'd also like to Email the completed report. Render seems to be the final step in a workflow, though, so it's not clear how to use the Email tool here.

[jrgo]

@Scott_Snowman,

You don’t mention this so just to make sure, did you pass the vendor data though a Table tool to convert it to a report object?

If you’re able to share some screenshots or, preferably, some files that will produce the issue to help take the guess work out, it would help to troubleshoot more efficiently.

in regards to email, you can set an event within the workflow configuration window (“Events” tab) that will send an email after a specific event. There’s an option that allows you to attach a file to that email. Use the same path you set your render to output to and set it to send after the workflow is complete. If you plan on putting this in a scheduler, use relative file paths in both the render and event and by relative, I mean no path; just specify the file name so it creates it in the same location of where the workflow is running from. This way, the event will know exactly where to pick the file up from.

hope this helps!

Jimmy

[Scott_Snowman]

@jrgo

Hello Jimmy, thanks for the reply! To answer your question: yes, I did place a Table tool before adding it into the render. Apologies for not attaching an example initially, I was in a hurry and thought I might be able to describe things enough without one.

I attached a mock-up of what I am working with. I've chosen a Union tool to stand-in for the QA engine in this workflow. In short, the engine takes 3 files as input, 2 of which are parameters that I provide and 1 of which our vendor provides. The engine essentially dispositions each record of the vendor data subject to the parameters defined in the 2 files. I use a series of filtering to isolate the records by disposition according to each QA rule we're enforcing.

The actual details are more complex but also not relevant to the workflow; the record-level output of the QA engine is fine. It's just the presentation I'm stuck on.

In the attached workflow mock-up, the problem is that the agency data just takes a long time to render. If I remove it, and only have the other 10 items going into the Render, the total time for QA and output takes ~30 seconds. However, then the QA file doesn't have a tab for the original data which is vital to cross-checking. Adding the data back in as shown in the workflow works, but takes up to 4 minutes. And this is for a relatively small dataset.

So what I'd like to do is tell Alteryx to:

1. Generate the output from the QA engine,
2. Render the 10 (9 + table of contents) QA-related tabs into a single .xlsx file,
3. AFTER 2. is done, then write the agency data back onto the output file as a final tab using an Output tool,
4. Email the complete file. (Looks like the 'Events' solution you described should work well.)

3 is where I'm stuck.

(Note that in the mockup the Layout tool can't be appropriately configured to assign the Sheet field to the name of the section because I deleted the structure of the upstream data, but again, that part is behaving properly.)

[jrgo]

@Scott_Snowman

So I tested a process that generates sheets via Render tool and then used a regular Output tool to add another sheet to the same file created by the Render tool.

Using a Block Until Done tool did allow both tools to complete without error, however, Excel did not like the table written by the Output tool. I received a message that Excel repaired the file. The sheets created by the Render tool were all there, but the data from the Output tool was cleared out... I assume by the repair.

Unfotrunately, no idea why this is the case and could possibly be a bug. It seems that the only way for it to work would be the approach you’re already attempting. Also can’t say why it added so much time when you added that data to your union. However, did see one thing in your Layout tool that looked a bit odd.

The list of report snippets that allows you to select the reports you want to arrange, I saw that there was one named ‘Sheets’. It’s not selected, however, this may be a clue to investigate WHY theres another report object being created using the same name that you’re wanting to create sheet names with.

Id start with the Union tool and make sure that it’s stacking your data properly. I could see this as an explanation to the process delay if a report object/snippet is being stacked with regular data and then being used to create page breaks in the Layout tool. 

[Scott_Snowman]

@jrgo

Thanks for the heads up, I'll need to take a closer look at the details of the Render tool when I'm able to get back into the workflow.

Would you mind sharing an example of how you were able to arrange the tools to accomplish the output using Block Until Done? I wasn't able to conceptualize how to get the process to work with multiple inputs (as opposed to one input stream being treated in two methods with Block Until Done).

[jrgo · Accepted solution]

@Scott_Snowman

 

It would be something like this. The append tool would be use as a second block and wont pass anything through until it the Summarize tool has been given data. The workflow that i tested this with was pretty small and didnt have nearly as many tools, but from my understanding of how the Block Until Done tool works, it should ensure that the output file is free from before the secondary outputs are processed.

As I mentioned though, while this was working for me, the table generated from the normal Output tool was triggering a "Repair" in Excel.

[Scott_Snowman]

@jrgo

Thank you for this. I checked the intermediate output of each tool in the workflow and it was behaving as expected. The output of all the tools was as expected with each intended sheet of the file being the same format moving into the next tool.

The time the workflow took to run was essentially constant until I added in the final Render tool, but after doing more testing this makes sense considering I was increasing the total number of records by about 1000x.

Thank you for the screenshot to address the Block Until Done with the use of a dummy summary. I had the same experience as you, with the output file being corrupt in some way. It didn't matter whether I was writing a new sheet or overwriting an old one; I still got the same issue. I'll have to pass all the records through the Render tool and just wait.

The Events tab worked but I couldn't customize the body of the email to include a summary of the data, so I am currently attempting to use the Email tool similarly after the Block Until Done with the Attachments parameter.

I'm tagging @MikeB in this thread due to a reply made on an apparently similar issue. Mike, a similar issue to one that jrgo uncovered here was discussed a while ago in this thread. If you are still looking for examples to reproduce the issue, the attached should be able to reproduce it easily.