Alternatives to changing input source for same workflow

[asp_ty]

Hi,

I've created a workflow that will perform some data manipulation to several of my reports each month and I was wondering if I have to manually open up the saved workflow and change the input files to the current month reports before i run the data manipulation workflow each month.

[NicoleJohnson]

Can you please provide a little more information about how your files are named? i.e. does the current file you wish to use contain some sort of date designation (like "File20170524")? If so, you could use a formula that comes up with the date designation based on today's date, or the date the workflow is run, and then use the Dynamic Input tool to get that specific file.

Alternatively, you might be able to use the Directory tool to bring in a list of all files in the source folder, then filter down for the most recent date in the LastWriteTime field (or CreationTime field). Then feed that file name into the Dynamic Input tool.

Let us know which scenario seems like the better fit, and if you can provide some dummy data, we can help model it in a little more detail? Thanks!

NJ

[asp_ty]

Hi Nicole,

Like you mentioned, the naming convention of the reports are identical except for the date (FileA201703, FileAReport201704, etc). Can you provide a link (if available) where I can read up on the formula approach or directory tool approach to gain a better understanding?

[NicoleJohnson · Accepted solution]

Here is a link to the Alteryx Help topic about the Dynamic Input tool:

https://help.alteryx.com/11.0/index.htm#DynamicInput.htm

As for the formula to get the file name and how that might work with Dynamic Input, I attached something pretty basic that should work:

1. Use the DateTimeNow tool to bring in today's date
2. Append the date to the list of your base file name(s): FileA, FileAReport, etc.
3. Formula to generate the file name, something like: FileName = [BaseFileName]+ToString(DateTimeFormat([DateTimeNow],"%Y%m"))+".xlsx" (the DateTimeFormat just converts today's date to the yyyymm format you need for your file), then sandwiches it between the base file name and whatever extension you're using, .xlsx/.csv/etc...
4. Run it through the Dynamic Input tool, selecting "Read a List of Data Sources", your generated FileName as the Field, and Action to Change File/Table Name. Note that if you're using Excel, you'll probably also need to add the Sheet name in your formula for FileName, I get hung up on that sometimes. Alternatively, you can "Modify SQL Query" in the second section and just replace only the part of your Input Data Source Template file address that references the file name.

Hope that helps! Let us know if you run into any more roadblocks or need some more assistance with Dynamic Input & formulas... Cheers!

NJ