Having issues adding a new file + table to email

[ianw91]

Hi,

I am creating a workflow where I have 2 sql queries - 1 query creates a file and writes it to a folder, and the other query's data is added as a table to the body of the email. I am trying to also attach the first file that gets written as an attachment to the email but I am having issues joining the data. I have tried to join on position but the inner join output anchor only shows 1 row of data. The left anchor shows an additional 3 rows, so these are getting excluded from the outputted file. Does anyone know of a better way to configure the join so that all data is included or should I be using a different tool?

Thanks!

[alexnajm · Accepted solution]

It depends on the end goal of your project, but I would take a look at a couple of things:

1. Check if you have a field to join on between the two stream - joining on record position will only join the first to the first, second to the second, etc.
2. Perhaps look at the  Append Fields Tool (alteryx.com) so that each record of one side gets added to every record of the other.

[Tam · Accepted solution]

Hi @ianw91 , 

I assumed the two queries are different to each other. If I understand you correctly you want Query 1 to be attached and Query 2 to be display in the body of the email. 

In your email tool: 

In attachment section you need the field containing the path to the dynamically named file. 

In the body section you need the field containing the layout 

to do this you need the append tool not the join tool.

You'll need to update the path for Query 1 to write to. 

HTH

Tam 

[ianw91]

Hi @Tam thanks for the help, once I added the append tool the data is flowing through.

One last question if you don't mind. I am now getting an error when the workflow attempts to write the file saying 'you must specify a sheet name'. 

I have specified a sheet name in the output tool in a format like test.xlsx|||sheet1 but the error is still the same.

Is this due to the fact that the file name is being dynamically named in the formula tool?

Here is the configuration of my output tool:

Thanks again for the help!

[alexnajm]

@ianw91 glad the initial Append Fields suggestion worked! Your Output Data looks correct - can you confirm the value in the "path" column by pasting it here? Otherwise, I would also look at changing the output option from "Create New Sheet" to "Overwrite Sheet"

[alexnajm]

Also that "|||sheet1" part needs to be cleared off if you are referencing that path column in the Email tool! For example, it would just be "test.xlsx"

[Tam]

@ianw91 please do me a favour mark @alexnajm original reply as an accepted solution where the append solution was given. 

In relation to your additional question. The workflow I originally posted in my reply saved the file to a CSV. I try to save to CSV unless there is a compelling reason to save to an XSLX. 

Saving to XLSX has the ability for you dynamically write groups of data in Alteryx output to different worksheet. Hence option 4 Append fieldname, to have the ability to dynamically name your output file you'll need to tick option 5 Skip Field Names. 

In the formula tool you need to create to two new variables: 

1: Path - This field will be use by the output tool to write to the XLSX file, an it requires the sheet name.  If there is ever only one sheet in the file then the formula should read: 

"\\some\where\directory\query1"+DateTimeFormat(DateTimeToday(),"%Y%m%d")+".xlsx"+"|||sheet1"

2: filename - This field will be use by the email tool to know which file to attach to the email. Notice the formula does not need the reference to the sheet name as pointed out in @alexnajm reply. 

Refer to an attached updated workflow.

HTH

regards 

Tam 

[alexnajm · Accepted solution]

@Tam , both of our responses can be marked as solutions 😊