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
PurpleKoolAid
7 - Meteor

I wish I would download this, but I cannot and am struggling. 

RCS
7 - Meteor

hey mohitb52,

 

Currently learning python and its wonders. its taking time though. I agree you can do something similar, but my idea was to cut out the middle man. The middle man being another piece of software.

 

The .bat script is native to windows and can be written in any text editor. Also you don't lose your template copy, its simply copied and pasted in a new location with an updated date stamp. Im developing  a macro right now that I'll try to release that will help the analyst where a few templates are given as options (or choose your own based on an old report but if you do it will run more script to clean it up so that its ready for the solution method using dynamic naming in my method for pivot tables), you select the finished file location, select where to put the template, make a report directory, and have the option of invoking mass email with text you want to write to the audience. This would basically be a big replacement macro for any out put tool and would include localized creation and deletion of .bat files to preform its operations). 

All thats left is designing your transformative part of the workflow and the data extraction part. Schedule that puppy, and now management gets their beautiful report on their own cadence.

 

I just worry about people getting spooked working with .bat files. If a distro went around and they didn't read the source code, it could be compromising. Perhaps a  checker tool that will only pass parameters to the run tools "write function" if the directory is a subdirectory of c:\users\joe.shmoe\documents\* or just check to make sure it not writing anything that is a technical folder like appdata, temp, windows, program data, program files &(x86), windows, etc. 

 

the end goal is to wrap this entire solution up into 1 macro, make it have better and easier functionality when selecting write locations , preserve safety while invoking 1 or more .bat scripts (there are evil people who solely manipulate these kinds  of things to get access or do damage by sharing modified versions where malicious cmdline code is replaced inside the macro since many ), and have it saved in a generated directory specifically for that report 

thuyduongnguyen
7 - Meteor

@mohitb52 Hi, thank you very much for your suggestion.

I wrote several lines of python code to change file name and it works.

My intention is to have the workflow gone through my excel file and create outputs. Then, in the end, the python code will change all files names at once. Therefore, I put this python tool at the 2nd anchor of "Block until done".

 

 

thuyduongnguyen_0-1617023288752.png

 

Labels