Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamically change file name while preserving pivot table integrity

RCS
7 - Meteor

Hi Everyone,

 

I'm working on automating a report from different pulled data sources I have. My issue is that when I try to dynamically change the file name to include today's date, the overwrite also deletes my tab with my pivot table.

 

I initially started with just having the manipulated data export to an output sheet with the pivot table tab, as the pivot table would be able to reference against the overwritten data sheets because the fields and field names were the same. After, the sheet refreshes the pivot table when it closes and is ready to be attached to an email.


I only have 2 logical directions on how to over come this problem. 1: find out how to overwrite sheets and dynamically change the file name while keeping the pivot tab. 2: Take the an output file were the pivot table tab correctly references the overwritten data and have alteryx take the output sheet and run a dynamic name change. 

 

If anyone has any ideas, that would be fantastic.

-R

12 REPLIES 12
patrick_digan
17 - Castor
17 - Castor

@RCS I would try something like the attached v11 workflow. Basically, I would use a run command tool to output your data (instead of the normal output tool). Then I just read back in the same excel file with only 1 record. Then I use a formula tool to create the bat script that copies the excel file and adds today's date. Then I use a run command tool to run the bat file that I'm creating.

 

Hopefully this points you in the right direction. As you've found out, when you write to the excel file and add the date in the name, it doesn't include your pivot table from your original file. The reason is that you're writing to a brand new file. It's not directly deleting anything, it's just writing to a new file that doesn't have the pivot tab from your original file.  

 

Let me know if you have any questions!

RCS
7 - Meteor

im having a difficult time implementing this into my workflow. Is there any way I can get more extensive help?

wjohndenham
7 - Meteor

@patrick_digan thank you for sharing this. Would you mind unpacking this a little bit more? I definitely need to save a new excel file each time while preserving pivot tables in my workbook. If you could provide some explanation to the logic/use of each tool in the workflow that would be really appreciated. 

patrick_digan
17 - Castor
17 - Castor

@wjohndenham We're trying to accomplish 2 tasks: Write the data out to a tab and then move that file in that order. So I use the first run command tool to write the data. In the read results section of the run command tool, I'm reading back in the first 1 row of the data. This run command tool gives us a way to output the data and then do further processing. If the normal output had a way to make sure it ran first, we would just use that. The run command tool is my best workaround. The formula builds the bat script to copy the file. The select tool just keeps our bat script. The second run command tool saves the bat script and executes it. The bat file is just copying the file and adding the date to the filename. 

RCS
7 - Meteor

@patrick_digan  @wjohndenham 

Hey so I posted this question awhile ago and just saw the feed back. Since July 2018 I've found some crafty ways to automate reports using some of the things ill mention in this post, including solving the initial problem I asked about. I have recreated a sample workflow with some data about cars for example purposes. 

 

Attached is a workflow in which 2 things are happening. 

1. A "Batch Runner" is being executed which takes a template "Shell" file with a pivot table that points to the a data sheet which has no data, only formatted field headers.

  -The template file is copied from a specified location and pasted with an attached date stamp in a file I designated "Finished Report."

2. A workflow that is transforming data and then being written to the data sheet in the date stamped copy of the template file.

-Since the file is date stamped, this process can be used to run a daily report without having to copy and rename output of an excel workbook generated by a workflow.

 

Things to Note:

>The template shell file has a pivot table using a "Defined Named" instead of a range to reference for data. A defined name is used because you can set it to be an offset formula that expands and contracts when rows or fields are added. (Making the reference dynamic) 

   This formula is used in the defined name *Data Source*: =OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),COUNTA(data!$1:$1))

 

Define Name.PNGPivotTableDSsetas'DataSource'.PNG

 

Then all you have to do is set the pivot table to be refreshed upon opening. And voilà, the report is automated taking into account changes in record lengths or added fields (if you are planning on overwriting the sheet instead of appending).

 

Once you have finished designing the pivot table with the data set, you go to the data tab where the data is being reference and delete all rows except your formatted field headers. You keep these because in the next step, you will be appending by field name and wont have to remove any formatting you put on those headers. Your shell file is now ready to be automated.

 

Next, follow the framework in the workflow I provided to execute a daily batch runner and then the workflow you want to populate results.

BatchRunner Plus Workflow.PNG

Take a look at the workflow and see what it is doing. The top part is a batch runner which copies the shell file into the empty "Finished Reports" folder so it may be appended by the second part of the workflow.

The second part of the workflow now has a file to write too because the output tool is set to use a field value as the directory. Luckily I was able to create a directory path with a date stamp so the output will point to the same shell file copied by the batch runner.

 

If cmd line and batch files dont make much sense, just think of them like this. Batch files (files with .bat extensions) are just instruction scripts that you are telling your computer to execute. In our scenario, the batch runner is writing to this .bat file the "instructions" to copy and paste a file we designated in the formula tool. The Run Command tool then can be used to point to a .bat file so alteryx can tell your computer "Hey!, follow these instructions. I want you to move a file located right here, make a copy, and paste it over there."  You dont need to edit a batch file at all in file explorer as alteryx creates and writes the instructions to the .bat file based on what you tell it to do in the formula tool within your workflow.

 

I used this method for a lot of reports I used to do that I now automated and scheduled on the alteryx server so I can work on bigger and better things! Now I don't even touch the files, the workflows do all the work and produce a multitude of reports daily. You can also use any input tools like sql or a dynamic file drop to pull last modified file in a folder.

 

Also to mention, I use a block until done and a dummy join to ensure that the cmd line executes the .bat file first so that the transformation workflow has a destination to write to. This will not work in other scenarios because of alteryx's order of operations when using larger data sets. My solve is just to have a "Batch Runner" create copies of all the reports I need to run at once, schedule that at like 1am, then have my workflows run after. *You could probably even use a conditional runner* Using a the "Label Block Until Done" tool from Crew will allow you to run a workflow with up to hundreds of run cmds. 

 

I will include all the files and examples, let me know if you have any questions. 

 

 

 

RCS
7 - Meteor

sorry, here is the corrected file with the workflow. I also removed the .bat file so your computer doesn't think its malicious. 

zbowden2010
6 - Meteoroid

This solution was really ingenious. Thank you so much.

RCS
7 - Meteor

thank you! if you need any more help with the nitty-gritty details, just let me know! i struggled for a long time and was so happy when i figured out this solution

mohitb52
5 - Atom

You can use python tool in developer window.

 

you just need to use the OS.rename(source,destination) function in python and rename you current excel file to the desired file.

 

Also if you want to keep the old file also in the path then just create a backup of that file using copy method in python Shutil.copy(src, destination)

Labels