Foreword by Mark Frisch
The Alteryx Community has afforded me many opportunities to grow my professional network around the world. In 2015 I invented “MarqueeCReW” and hid behind this identity in the Alteryx Community. Focused on sharing Alteryx wisdom and helping others solve their challenging issues, MarqueeCReW is a known by many friends of Alteryx. The surprise for me is always present, as when I met Didier in Amsterdam at #AlteryxInspire22 and he knew who I was. We started talking about solving “hard” challenges with “dynamic” solutions.
In client engagements, I work to create solutions that are “easy” to maintain. There needs to be a level of flexibility in the work so as not to create a fragile solution that requires my regular attention. So are the workflows that are tied to Excel inputs. One little change can ruin a defenseless workflow. A more critical challenge is writing to an Excel workbook and having to survive updates to that worksheet that occur outside the control of your workflow.
Didier reviewed his workflow with me, and we both recognized challenges for a dynamic process and how each of us handled the situation. In some cases, we were identical and in others I was pleased to see new ways of accomplishing the same goal. One such example was the use of a RENDER tool to create new/missing directories. Instead of using a feared RUN Command, a directory can be created with the use of a Render tool. Days before our meeting I recognized a pattern of Alteryx design where two Union tools were paired to both ENSURE data presence and to add Optional fields. I immediately knew the trick that Didier was about to show me.
I encourage you to explore the treasures awaiting you in this process. Open the macros and see what is going on under the covers. Learn new techniques for solving manual challenges with a dynamic approach. Give your feedback to Didier and encourage him to post tutorial videos. Better yet, make friends with him in real-life like I was fortunate enough to become.
The .xlsx formatting challenges
The Alteryx Reporting tools are very good to enhance seamlessly the presentation of the results of our analytical processes. In a few clicks, they format tables and graphs which are rendered in a large choice of file formats.
When you are requested to present your data in the format requested by your board, auditors or by the regulator, you may find very cumbersome to adjust the parameters of the Table tool to get the right column width and height, borders… This was the challenge, we had to solve when producing reporting for the fiscal department of a bank.
In the case of outputs to be used by MS Power BI or other data visualisation tools, you may not care about the format but complying to the name of the columns is another challenge.
When the format is changing, it is even worse! You must adjust again the formatting in the workflow. If you have distributed your workflow on your private Gallery, you’ll have to modify and republish the workflow delaying the production of the report.
The template suite
To facilitate the compliance to Excel format requirement and give more autonomy to the end user, we have developed the Template suite made of a main tool, “Template” completed with the “Copy file” tool. These two tools are coming along with the “Create Folder” and “File info” and “Block until file ready” tools.
The process consists in two steps:
1. Prepare the MS Excel template file with the column’s headers and without data.
2. The “Template” tool will copy the template file as the final output file and print the data on it preserving the column order and the format.
Template design
This part is ultimately entirely controlled by the end user:
1. Save in the input folder of your use case, a copy of a sample output MS Excel file.
2. Remove all the data keeping only the top header.
You can set dynamic formatting to have borders around the data.
Output workbook creation
The “Template” tool is used like an “Output Data” tool. It can handle relative thanks to the “Relative to absolute path” tool.
You need to change the format, to add a new column, to remove one or to change the order? Just update the template file accordingly, the “Template” tool will do the rest.
If the .xlsx template contains several worksheets to update, the “Copy file” tool is used to create the final workbook which is then updated with as many “Template” tools as worksheet to complete.
If the column headers are not the same as the name of the fields of your Alteryx process, you put the later in a line under the column header.
In the interface of the “Template” tool, you’ll then, “Check this box if you want to override the field names used by the Template tool”.
Do your thing now!
You can download the Template suite yxi package as well as a example show casing it.
To use it, change the .zip extension to .yxi and double click it to install the PA tool bar and get back to us your comment and suggestions at alteryx@primenalytics.fr.
Acknowledgements
We thank Asta Soiniitty with whom the first version of the “Template use” has been created, Mokhtar Bichiou for the folder creation tip, Ana Costăngioară for correcting the tool suite, Robert Sobolewski who proposed the solution used to make sure that the files are available and Mark Frisch for his kind review and improvements.
2023-03