Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Writing output to excel file with Pivots

Nikita-Puniani
7 - Meteor

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?

4 REPLIES 4
Luke_C
17 - Castor

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:

 

  1. Make a copy of the template file
  2. Output the data 
  3. 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-Overw...

 

 

 

Nikita-Puniani
7 - Meteor

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

Luke_C
17 - Castor

Hi @Nikita-Puniani 

 

The nice thing is the bat files are totally dynamic! You'd just need to update the file names or paths in the formula tools:

 

In this example it's dynamically pulling the workflow's directory to start, but you could hardcode the entire directory if preferred.

 

Luke_C_0-1617991093961.png

 

Nikita-Puniani
7 - Meteor

Thanks for the explanation. It worked as expected. thanks!

Labels