Dynamic Column Name and Dynamic Output attachment on Email (.xlsx file)

Question

Hi,

I have a query regarding dynamic column names. For eg: I have 3 columns: "April Month End", "March Month End" & "February Month End", keeping in mind all of these columns are dynamic, if March would have been the current month then these three columns would have been for March, February & January. So, to this(current month - April) I want to add one more column that is named "Consolidated Feb'21 - Mar '21 - Apr '21 Excess".

Moreover, since I'm relatively new to this software, is it possible for Alteryx to send an email with dynamic attachments (mine is a .xlsx file) on it? That is, my filename changes according to the date, and I want Alteryx to send an email on daily basis with the current date filename (concatenated) as an attachment. The workflow is already been made by me but I'm stuck at the dynamic attachment. Lastly, to be honest, the excel file which I'm sending has 2 sheets, the first sheet has all base data and the second sheet is a pivot type table. I was wondering if this pivot type table can be added to the body of the email (maybe as an image) which I'm sending.

Many Thanks!

BenMoss · Accepted Answer

Hi!

If you want to include your pivot table in the image body then you need to look at the reporting tools, and specifically the table tool, this 'reporting object' that you create can then be passed into the body of your email.

Regards your attachment, the email tool allows you to select your file based on a formula, my advice would be that you add your dynamic filename as a field to your data and then the attachment can reference said field, and your output tool can as well using the options available at the bottom of the output data tool.

Ben

danilang · Accepted Answer

Hi @sanketkatoch05

To answer the first question in your post, here's how you can add a consolidated column with a dynamic name based on dynamic input columns

First you transpose all the data columns to one name\value pair of columns.  Then apply a consolidation function to your value column.  In this simply example I just summed all the values. You can replace the summarize tool with any sequence of tools that gets the answer you need.

To build the dynamic column name, add sequential number to each data column and then get the unique column names.  Sort them in desc order so the first column is now the last and build a Feb'21 type string for each column.  Build the dynamic column name using the concat function in the summarize tool.  Use a Dynamic Rename to change the name of the consolidation column to the dynamic one and then join back to your original data

Dan