Writing output to excel file with Pivots

I have a process that I am trying to transition to Alteryx.

The process begins with running a stored procedure which then writes the data into excel spreadsheet (sheet 1)

The excel has some pivot tables in other tabs(sheet 2). Currently the excel file is connected to a backend database. As soon as the data is refreshed in the table, Sheet 1 and pivots(Sheet 2) all get updated.

The requirement is that this process be run on a daily frequency and the pivot automatically gets refreshed for THAT DAY only when the raw data is loaded(sheet 1). Each day will be its own separate file. What I don't want is today's data to update historical versions of the file when opened. How can i move this process to Alteryx?

Input

Output

Database Connection

Accepted answers

Luke_C

Hi @Nikita-Puniani

What I would recommend is making use of a 'template file'. This template would have the structure of the pivots and the data tab for alteryx to output into. You would want to make sure the output tool is configured to append to that data sheet rather than overwrite it in order for your pivots to still work.

From there, you would make use of the run command tools to do the following when the workflow is ran each day:

Make a copy of the template file
Output the data
Rename the file with todays date (or whatever naming convention you use)

I have an example of this in this thread you could reference:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clearing-Data-in-Template-before-Overwrite/m-p/738713#M176090

All comments

Luke_C

Hi @Nikita-Puniani

From there, you would make use of the run command tools to do the following when the workflow is ran each day:

Make a copy of the template file
Output the data
Rename the file with todays date (or whatever naming convention you use)

I have an example of this in this thread you could reference:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clearing-Data-in-Template-before-Overwrite/m-p/738713#M176090

Nikita-Puniani

Thanks @Luke_C for the solution. This may resolve the issue

I am new to using run command. I imported your solution and replicated this process using my set of files. Here is the Error message I got - "The external program "%temp%\Rename.bat" returned an error code 1".

I have a quick question. It seems like both the Copy.bat and Rename.bat are running from the default temp folder. Perhaps it is failing because the filenames may need to be changed within the .bat files. I am not able to locate these files in my temp or any user folders.. Could you share the code used within the Copy and Rename.bat files please?

Really appreciate your help!

Thank you

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703