Change Entire File Path for Date AFTER .xlsx File Name + Sheet

[valeriemattas]

Greetings!

I have two questions: 

1) I am using "Change Entire File Path" output function with a filter for the file path name (see fillter below) and it works/saves the file where I want it as expected. However, is there a way to have the date populate AFTER the file name rather than before it? Such as File_Name_2020-04-09.xlsx"

"File_Path\" + DateTimeFormat(datetimetoday(),"%Y_%m_%d") + "File_Name.xlsx|||Sheet_Name"

2) Part of my workflow has an output of a summary table and the supporting tables are each split into separate sheets in the output file using Take File/Table Name from Field + Append Suffix to File/Table Name. However, if I want these sheets to be added to the same file with a dynamic date (referenced in question #2 above) is that possible? Since I'm already using that tool to split the data into results, I can't add the Change Entire File Path function as well...

Thanks!

Valerie 

[AbhilashR · Accepted solution]

Hi Valerie, 

I have put together a sample solution to help illustrate achieve the function you are looking for.

For the first question, change your FilePath formula to something like:

".\File_Name_"+DateTimeFormat(datetimetoday(),"%Y_%m_%d") +".xlsx|||Sheet_Name"

For second question, you can write your FilePath formula to look something like - [TabName] could be some field in your data.

".\File_Name2_"+DateTimeFormat(datetimetoday(),"%Y_%m_%d") +".xlsx|||"+[TabName]

For this approach to work, you will have to modify the Output Option to Overwrite Sheet (Drop) option.

This is to ensure Alteryx retains the excel file, and only updates the tab. As opposed to overwriting the file, should you choose Overwrite File (Remove) option. Hope this helps.

[AbhilashR]

Hi Valerie,

For the first question - tweak your formula to look something like:

".\File_Name_"+DateTimeFormat(datetimetoday(),"%Y_%m_%d") +".xlsx|||Sheet_Name"

For the second question - you could write your formula to look something like:

".\File_Name2_"+DateTimeFormat(datetimetoday(),"%Y_%m_%d") +".xlsx|||"+[TabName]

For the second solution to work, you will have to select Overwrite File (Remove) in the Output Options of the Output tool.

Selecting this option tells Alteryx to only update the sheet/tab and not the whole file.

Hope this helps. 

[valeriemattas]

@AbhilashR 

Thank you! The solution for #1 worked perfectly. 

Regarding #2 - I am able to get the two workflows to combine into one excel file. The issue is, I want to split the results from the 2nd workflow into multiple tabs, not just one additional tab on the new file, which is why I was using Append Suffix to Table/File Name. 

Is there a way to keep the file names the same and get the 2nd workflow to break up the data into multiple tabs?

[AbhilashR · Accepted solution]

Hi @valeriemattas, referring to the previous solution I had included, the stream along the second output tool outputs data to the same physical file but multiple tabs. It splits the data into multiple tabs (Anne, Jack, John) in this case. Is that not what you are looking for?

[valeriemattas]

@AbhilashR - Thanks for your patience and your help! It is still not doing what I am trying to accomplish. 

To continue with your example, I have two sets of results: 

FILEPATH - Summary of records A-Z

FILEPATH_NEW - Detail of records 1-10 (has different fields than FILEPATH)

What I am looking for is to combine the results as follows in one file: 

Results of FILEPATH (Summary A-Z) on one tab

Detail of 1 on a separate tab

Detail of 2 on a separate tab

Detail of 3 on a separate tab

... etc. through 10 on a separate tab

I'm trying to break up the second file into multiple tabs (based on a field) and add it to the first file without the first file being changed. The only way I've been able to accomplish this is to create 10 different filters (one for each Detail #) and then perform the solution you provided. 

Hope this makes sense. 🙂 

[AbhilashR]

Hi @valeriemattas, something like the attached solution work in your case? Both streams write to the same file, which is what I gather you are looking to do.

[valeriemattas]

@AbhilashR  No, unfortunately, that does not split the second results file up into multiple tabs, it adds it all as one tab to the same file as results from the first workflow. 

Thanks for your help.

[AbhilashR]

Hi! Can you share the file you get from running the solution I sent? Below is a screenshot of the output I get.

[valeriemattas]

@AbhilashR  I finally figured it out. The Field I was trying to use for the "Tab" was an integer. I changed the field to a V_String and it worked. 

Thanks for your help!