Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Dynamically Input Last Month's Daily Files to Combine into One Monthly File

Highlighted
5 - Atom

Hello!

 

I am trying to get Alteryx to dynamically combine the last month of daily .txt files into one monthly file.

 

Each day, a report is automatically dropped into a folder. This folder holds daily files from an entire year (so, a rolling 365 files always). I need to look at July 2020's daily files and combine them into one "monthly" file. I will need to do this for every month going forward. My goal is to automate this process on the 1st day of every month. So, on September 1, alteryx would automatically create this monthly file for August. 

 

For example, the 7/1/2020 file name would look like "Report_20200701_to_20200701.txt" (I did not choose this name and can't change it unfortunately). The date in the file name will correspond to the date it was created. The CreationTime field, of course, would also correspond. The number of files for that month would equal the number of days in the month. For July, I would want the 31 files, starting with "Report_20200701_to_20200701" through "Report_20200731_to_20200731". Then, these files would be combined into "Report_072020" (in txt or xlsx) or something. I don't even care if they're written in order, although that could be nice.

 

What is the best way to get Alteryx to look into that folder path, find the most recent month's files, and combine them into one?

 

 

Highlighted
Alteryx
Alteryx

You can use a directory tool to pull in the list of file names in a folder, extract the date from the file name, use a date time tool to convert to a date, filter date for the current month, and then feed the results into a dynamic input tool. This should stack all of the data that you are looking for. 

Highlighted
5 - Atom

Thank you for answering Brandon!! I appreciate your help. How would I extract the date from the file name? I'm also not sure I understand what the formula would look like to pull current month. I want to be able to run it every month without editing this formula, if possible.

Highlighted
Alteryx
Alteryx

Here you go. Basically you just use a formula tool to pull the date from the file name using Left and Right functions. Then you use a date time tool to convert the extract to a date. Then you dynamically pull in the number of last month using DateTimeMonth(DateTimeNow()) - 1. Then you take another DateTimeMonth and apply it to the date of the file. Finally a filter where last month = month of file will only allow files to pass through from the previous month. You then feed in the list of file paths into a dynamic input tool and it will stack them all. Workflow is attached for reference. 

 

month extraction.png

Highlighted
Alteryx
Alteryx

In my example, nothing will need changed month over month, because the DateTimeNow() function pulls in the current date time every time the workflow is done. That means your process will be dynamic!

Highlighted
5 - Atom

This worked perfectly! Some of my files ended with .txt and some with .txt.gz so I just duplicated the workflow, tweaked the date extract formula for one of the flows, and then used the union tool to bring the flows together into one file. Thank you so much!!!!

Labels