Evergreen Excel Dataset

[KLEITH21]

Hello,

I receive monthly data and am looking for the best solution to keeping a continuous and evergreen EXCEL workbook. The table would look something like this, with a new column of data to the right of the UID each new month the workflow is run:

UID	Jan Data
1	x
2	x

UID	Jan Data	Feb Data
1	x	x
2	x	x

UID	Jan Data	Feb Data	Mar Data
1	x	x	x
2	x	x	x

...and so on...

I can only seem to replace and NOT preserve the Jan Data column when I attempt to Join Feb data. Any suggestions?

[amruthas2]

Hi @KLEITH21 

Consideration -
Output File has just UID & Jan Data for 'Jan', UID - Feb Data for 'Feb' and so on

Every month,

1. add new month table to alteryx

2. build connection to Exisitng 'Union' from new table

3. add that year field as Sum in 'Sumarization' function

Please let me know if this helps.

[danilang · Accepted solution]

Hi @KLEITH21 

Here's a dynamic method that will allow you run the workflow monthly without any changes to the workflow(after the initial run).

The workflow reads the data from the latest excel file((bottom input) and uses a dynamic rename to change the name of the new data column to the file name which I've set to the month name.  This data is joined to the data in the existing evergreen file to add a new column in the last position.

The first time you run this, disable the container called "Enable on subsequent runs" and enable the the container called "Enable on first run only".   Run this with your first file to create the initial Evergreen file.  Once you've done this, disable the "Enable on first run" container and enable the other container.   From now on, run this as an analytic app and use the file browse tool to select the next monthly file.  The data column from new file will be added to end of the columns with the new month name. 

Here's a sample of running this for the first 3 months.  

Month 1(initial run)

Month 2 (as analytic app)

Month 3

I use the file name as the column header for simplicity.  You can either rename the monthly files before you run the workflow or modify the column logic to work with the data that you have. 

Attached is the workflow and the 3 sample files that I'm using

Dan

[KLEITH21]

Each month I need to take the running average of the last 3 months. Now that you see my resulting schema, is it possible to run a formula across "Last N" columns or would I have to transpose this data? If so the Transpose tool looks static/manual and not dynamic. Any suggestions? @danilang @amruthas2