Dynamically change file with multiple tabs

[peterg97]

Hi, 

I am building a workflow, and it will need updated every month with the new file. I will be manipulating the data from 2 seperate  tabs in the file, and then joining it together. I would like this to be as dynamic as possible for when someone else has to use it, for example when they run as analytic app, they will just have to select the new file path and the rest will work itself. At the moment, if I run as analytic app, I can choose the file path but after I run, the directory hasn't changed. Ideally, if there is a way to dynamically select a file and then have separate processes for the different tabs, that would work best. Any ideas?

Thanks!

 

[christine_assaad]

Hi @peterg97 

You can continue to use "Directory" then you can :

1- Perhaps add a "Filter" tool to select the desired file based on its name. For example if it includes month name, you can use a formula that dynamically looks at today's date to find last month.

2- You can add a "Sort" based on last write time/creation time (newest to oldest) and with a Sample tool of 1, you will be sure you are looking at the most updated file.

These are some ideas.. Hope that helps. Cheers!

[peterg97]

Hi @christine_assaad

Thanks for helping out! Unfortunately he file folder won't be the same for each month; Each month will have its own overarching folder, with this file being one of a few in the individual folder. I think my ideal way would be to not use a directory, but instead, just update the file, but then that brings up the issue of having to input that file twice in order to work on the 2 tabs. 

Kind Regards, 

Peter. 

[pedrodrfaria]

Hi @peterg97 

I have attached an example of what you can do:

You insert the file and select to output the sheet names, we then build the file path for both sheet names and then separate them into two different workflows using the sample ( you can use the filter to apply name filtering logic) and then this macro (included in the workflow) to load both files (it covers all schemas for xlsx). This macro can be updated to digest other files, would just need to update the macro from inside. Let me know if you have any questions.

Pedro.

[peterg97]

Hi @pedrodrfaria, 

Thanks for the help! Unfortunately I cannot open the workflow that you attached (I think my Alteryx is out of date for it). For me, the input file is coming up as a table, not query (see below), and the formula is showing as an error (see below). Any guidance would be really appreciated, i'm new to this!

Kind Regards, 

Peter. 

[pedrodrfaria · Accepted solution]

I have added below some pictures to walk you through the configuration of each building block. I also added the macro i mentioned. If you are having problems opening up a workflow that was built with a different version of Alteryx, you can right click it, open as a notepad, find where it says the version the workflow was built and update to your version, save it and try reopening the workflow inside of alteryx and you should be successful. 

[peterg97]

Hi @pedrodrfaria, 

Works perfectly! Thanks for your help, I really appreciate it!